Re: can't get join on two large tables to use_nl or indexes

Woo-hoo, got it to work,

If I reverse the columns in the index I get the performance I'm
looking for. In other words the behavior I am observing is:

If the column with the handful of distinct values is used first in the
index the index is ignored in spite of several attemts to get it
recognized.

If the column with the number of distinct values of about 1/10 the
number of rows in the table is used first in the index then the index
is used if I do something like the following:

select
/*+ index(ccp ccp_sid_pid)*/
*
from
str,
ccp
where
str.sid = ccp.sid
and property = 'FOO'

SELECT STATEMENT Optimizer=CHOOSE (Cost=3941922 Card=953924
Bytes=915767040)
NESTED LOOPS (Cost=3941922 Card=953924 Bytes=915767040)
TABLE ACCESS (FULL) OF STR (Cost=60153 Card=1293923
Bytes=1212405851)
TABLE ACCESS (BY GLOBAL INDEX ROWID) OF CCP (Cost=3 Card=1
Bytes=23)
INDEX (UNIQUE SCAN) OF CCP_SID_PID (UNIQUE) (Cost=2 Card=1)

This query returns data in about 30 msec v several minutes!

I am supprized at the cost (3941922) given that I get first rows so
quickly.

If I do a select count(*) from the above query and add RULE to the
hint I get back the count (about 1million) in about 15 seconds (I'm
guessing that this is a good approximation of how long it will take
Oracle to access all of the rows?). It takes about 1 sec to get back
the first 10k rows. Fortunately, most of the people looking at these
data will only be interested in the first rows (generally < the first
10k rows.

One more minor detail: the rule uses the alias of the table.

This ignores the index:

select
/*+ index(my_table my_index) */
*
from
my_table mt,
my_other_table mot
where
mt.motid = mot.motid
and some_col = 'FOO'

This uses the index:

select
/*+ index(mt my_index) */
*
from
my_table mt,
my_other_table mot
where
mt.motid = mot.motid
and some_col = 'FOO'

Can someone recommend a good book(s) or other resouce(s) that provides
(preferably) comprehensive information on Oracle query (and database)
tuning as well as act as a refernce for quick info regarding specific
questions for Oracle 10g as well as older implementations
(specifically 9i)?

Thanks,
John


.

Relevant Pages

  • Re: Strange query - Oracle Text problems?
    ... below) just to show that in this case, the query proceeds efficiently. ... FROM FOO ... With the Partitioning, Oracle Label Security, OLAP and Oracle Data ... SQL> DROP TABLE FOO; ...
    (comp.databases.oracle.server)
  • Re: Views - performance
    ... when u use stored object views oracle has to read the data dictionary ... this includes lots of steps for execution ... semantics of the query and fires it ... you'll possibly have any idea is to use explain plan to provide a base ...
    (comp.databases.oracle.server)
  • RE: DBI-1.48 bind_param_inout produces ORA-01461 colliding with a CLOB
    ... can pass strings as varchar's and have them inserted ... In theory I could write something to query the ... CLOB w/in Oracle is probably what is killing me... ...
    (perl.dbi.users)
  • Re: Equilevant of Oracle for optional records
    ... this query is done, then flatten the result? ... example, the Iqaluit Trauma: ... Oracle handles this data structure perfectly but I suspect it might be ...
    (microsoft.public.access.queries)
  • Re: Updatable ... not
    ... I have code in the front-end that imports data from csv files into ... The back-end also links to Oracle tables for data matching and ... work on data in the Oracle table. ... I built a new query, just like the original query, and no, I still ...
    (microsoft.public.access.modulesdaovba)