Why does this oracle select statement take minutes to complete?

These tables have less than 20 fields, properties has about 9 million rows while listings has 3 million rows, but that should not be a problem. This is what databases are for...

the listing_ids are of type Number. Our best guess so far is that because the properties table has 6 million more rows with listingids that dont actually point to a listing, Oracle spends a lot of time lo开发者_JS百科oking for listings that dont exist. Does that even make sense?

Select  count(*) 
from listings.rfs_listings listings  
    join listings.rfs_properties properties 
        on listings.listing_id= properties.listing_id        
where listings.display = 1  
    and properties.city= 'New York'
    and rownum <= 10;

I ran an explain plan on the query and got the following info:

PLAN_TABLE_OUTPUT

Plan hash value: 772088252

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                             |    10 |  4110 |   283   (0)| 00:00:04 |
|*  1 |  COUNT STOPKEY                |                             |       |       |            |          |
|   2 |   NESTED LOOPS                |                             |       |       |            |          |
|   3 |    NESTED LOOPS               |                             |    10 |  4110 |   283   (0)| 00:00:04 |
|*  4 |     TABLE ACCESS FULL         | RFS_LISTINGS                |   140 |  2940 |     3   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN         | RFS_PROPERTIES_LD730_UNIQUE |     1 |       |     1   (0)| 00:00:01 |
|*  6 |    TABLE ACCESS BY INDEX ROWID| RFS_PROPERTIES              |     1 |   390 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=10)
   4 - filter("LISTINGS"."DISPLAY"=1)
   5 - access("LISTINGS"."LISTING_ID"="PROPERTIES"."LISTING_ID")
   6 - filter(NLSSORT("PROPERTIES"."CITY",'nls_sort=''BINARY_CI''')=HEXTORAW('6E657720796F726
      B00') )

EDIT: Table Schemas:

rfs.rfs_listings:
Name                            Null?    Type
------------------------------- -------- ------------------
DETAIL_ID                       NOT NULL NUMBER
LISTING_ID                      NOT NULL NUMBER
DETAIL_CHECKSUM                 NOT NULL VARCHAR2(32 CHAR)
PRICE                                    NUMBER
IN_CONTRACT                     NOT NULL NUMBER
CREATED                         NOT NULL DATE
PROPERTY_WEB_ID                 NOT NULL VARCHAR2(100)
SOURCE_ID                       NOT NULL NUMBER
LISTING_CREATED                 NOT NULL DATE
ARCHIVE_NAME                             VARCHAR2(100)
DUPLICATES_GROUP_ID                      NUMBER
FILENAME                                 VARCHAR2(80)
DISPLAY                                  NUMBER


rfs.rfs_properties:
Name                             Null?    Type
------------------------------- -------- -----------------
PROPERTY_ID                     NOT NULL NUMBER
LISTING_ID                      NOT NULL NUMBER
BLDG_PROPKEY                             NUMBER
UNIT_PROPKEY                             NUMBER
ADDRESSKEY                               NUMBER
HOUSE_NUMBER                             VARCHAR2(32)
STREET_ADDRESS                           VARCHAR2(200)
UNIT_NUMBER                              VARCHAR2(32)
UNIT_NUMBER_PARSED                       VARCHAR2(16 CHAR)
PARSED_ADDRESS                           VARCHAR2(255)
DISPLAY_ADDRESS                          VARCHAR2(150)
CROSS_STREET                             VARCHAR2(200)
NEIGHBORHOOD                             VARCHAR2(150)
NEIGHBORHOOD_CODE                        NUMBER
NEIGHBORHOOD_REG_CODE                    NUMBER
SCHOOL_DISTRICT                          VARCHAR2(100)
BOROUGH_CITY                             VARCHAR2(100 CHAR)
METRO_AREA                               VARCHAR2(100 CHAR)
ZIP_CODE                                 NUMBER
COUNTY                                   NUMBER
STATE                                    VARCHAR2(4 CHAR)
ROOMS                                    NUMBER
BEDROOMS                                 NUMBER
BATHROOMS                                NUMBER
SQFT                                     NUMBER
LOT_SIZE                                 NUMBER
STUDIO                                   NUMBER
LOFT                                     NUMBER
MAINT_CC                                 NUMBER
RE_TAX                                   NUMBER
PROPERTY_TYPE_ID                         NUMBER
PROPERTY_TYPE                            VARCHAR2(255)
BLDG_NAME                                VARCHAR2(255)
BLDG_TYPE                                VARCHAR2(32 CHAR)
BLDG_NEW_DEVEL                           NUMBER
BLDG_FEATURES                            VARCHAR2(256)
MANUALLY_BLDG_FEAT                       VARCHAR2(255)
APT_FEATURES                             VARCHAR2(256)
OUTDOOR_SPACE                            VARCHAR2(32 CHAR)
YEAR_BUILT                               NUMBER
LISTING_RANK                    NOT NULL NUMBER
LOCATION_CHECKED                         NUMBER
PROPKEY_SOURCE                           VARCHAR2(15)
WEB_BUG_URL                              VARCHAR2(255)
EMAIL_LEAD_GENERATION                    VARCHAR2(100)
LISTING_URL                              VARCHAR2(255)
BROKER_NAME                              VARCHAR2(100)
BROKER_URL                               VARCHAR2(256)
LISTING_TEXT                             CLOB
IS_UPDATED                               NUMBER
CENTROID_X                               NUMBER(20,10)
CENTROID_Y                               NUMBER(20,10)
CENTROID                                 MDSYS.SDO_GEOMETRY
COUNTY_GEO_ID                            NUMBER
CX                                       NUMBER
CY                                       NUMBER

Some updated Statistics after updating oracle stats:

Statistics

     31  recursive calls
      2  db block gets
  63053  consistent gets
  15474  physical reads
      0  redo size
   2890  bytes sent via SQL*Net to client
    524  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
     10  rows processed

New Execution Plan After Updating Statistics:

Execution Plan
----------------------------------------------------------
Plan hash value: 3213592672

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                             |    10 |  4110 |   236   (0)| 00:00:03 |
|*  1 |  COUNT STOPKEY                 |                             |       |       |            |          |
|   2 |   NESTED LOOPS                 |                             |       |       |            |          |
|   3 |    NESTED LOOPS                |                             |    10 |  4110 |   236   (0)| 00:00:03 |
|   4 |     TABLE ACCESS BY INDEX ROWID| RFS_LISTINGS                |   224 |  4704 |    11   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | RFS_LISTINGS_DISPLAY        |       |       |     3   (0)| 00:00:01 |
|*  6 |     INDEX UNIQUE SCAN          | RFS_PROPERTIES_LD730_UNIQUE |     1 |       |     1   (0)| 00:00:01 |
|*  7 |    TABLE ACCESS BY INDEX ROWID | RFS_PROPERTIES              |     1 |   390 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=10)
   5 - access("LISTINGS"."DISPLAY"=1)
   6 - access("LISTINGS"."LISTING_ID"="PROPERTIES"."LISTING_ID")
   7 - filter(NLSSORT("PROPERTIES"."BOROUGH_CITY",'nls_sort=''BINARY_CI''')=HEXTORAW('6E657720796F726B
          00') )

---

Your query plan suggests that Oracle believes there are 140 rows in RFS_LISTINGS where DISPLAY=1, not millions. To get better optimisation you need to gather some better stats.

To clarify, I mean you should run the DBMS_STATS package so that Oracle (not just you) knows how much data it is dealing with e.g.:

exec dbms_stats.gather_schema_stats ('LISTINGS');

Talk to your DBA first.

---

you will want indexing on the WHERE columns...

listings.display = 1  
properties.city= 'New York'

---

Could be because ROWNUM is evaluated after all data is selected. Plus, as Randy said, you're gonna need indexes, but by the plan, it looks like you have them.

---

I think what you need is an index for LISTING_ID on each table. This should prevent the full table scan from occurring.