Home » SQL & PL/SQL » SQL & PL/SQL » Optimizing query with alpanumeric range condition
Optimizing query with alpanumeric range condition [message #209144] Wed, 13 December 2006 08:56 Go to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
I have been working on this query for about a day at this point and the basics of it are simple enough, but it is taking in intolerably long amount of time to run. For the setup these are my tables and the indexes on those tables, for the sake of this example if two columns in different tables have the same name, they can be linked to each other.

create table leaf1 (
	leaf1_id number
	leaf1_name varchar2(15)
)

create unique index leaf1_u1 on leaf1(leaf1_id)
create unique index leaf1_u2 on leaf1(leaf1_name)

create table leaf2 (
	leaf2_id number
	leaf2_name varchar2(15)
)

create unique index leaf2_u1 on leaf2(leaf2_id)
create unique index leaf2_u2 on leaf2(leaf2_name)

create table trunk1 (
	   trunk1_id number
	   leaf1_id number
	   null1 number
	   null2 number
)

--null1 and null2 are unused in our implementation, 
--but they are part of the index 

create unique index trunk1_u1 on trunk1(trunk1_id)
create index trunk1_n1 on trunk1(leaf1_id, null1, null2)

create table trunk2 (
	   trunk1_id number
	   leaf1_id number
	   leaf2_id number
	   trunk2_line number
	   trunk3_line number
	   trunk2_con1 varchar2(3)
	   trunk2_con2 varchar2(3)
)

--trunk2_con1 and trunk2_con2 are static constants which 
--for this query have a hard coded value

create index trunk2_n1 on trunk2(trunk1_id, trunk2_line, trunk2_con1, trunk2_con2)
create index trunk2_n2 on trunk2(trunk2_con1, leaf1_id, leaf2_id)

create table trunk3 (
	   trunk1_id number
	   trunk3_line number
	   leaf1_id number
	   trunk3_date date
	   trunk3_con1 varchar2(3)
	   trunk3_con2 varchar2(3)
) 

--trunk3_con1 has a hard coded value for the purpose of this
--query, trunk2_con2 is unkown ie all values are valid

create unique index trunk3_u1 on trunk3(trunk1_id, trunk3_line)
create index trunk3_n1 on trunk3(leaf1_id, trunk3_con1, trunk3_con2)
create index trunk3_n2 on trunk3(trunk3_date)


Now leaf1 and leaf2 both have about 5000 rows
trunk1 has about 800,000 rows
trunk2 has about 1.2 million rows, after hard coded restrictions has about 880,000 rows
trunk3 has about 1.1 million rows, after hard coded restrictions has about 880,000 rows

On top of all this, my conditions are a bit of a pain, I have six parameters, which represent 3 ranges.

I have a alphanumeric range for leaf1.leaf1_name, another alphanumeric range for leaf2.leaf2_name, and a date range for trunk3_date which is passed in the month for so something like 'JAN-05' to 'JAN-06'. Either of the first two pairs may be null on whole or in part, the final pairing on date is required. In most cases only one of the non-required pairings will be provided

One starting point query for the problem follows:

select leaf1.leaf1_name, leaf2.leaf2_name, trunk1.trunk1_id
from leaf1, leaf2, trunk1, trunk2, trunk3
where trunk1.trunk1_id =  trunk2.trunk1_id
and trunk1.trunk1_id = trunk3.trunk1_id
and trunk3.trunk3_line = trunk2.trunk3_line
and trunk1.leaf1_id = leaf1.leaf1_id
and trunk2.leaf2_id = leaf2.leaf2_id
and trunk2.trunk2_con1 = 'CON'
and trunk2.trunk2_con2 = 'CON'
and trunk3.trunk3_con1 = 'CON'
and (:leaf1_start is null or :leaf1_start <= leaf1.leaf1_name)
and (:leaf2_start is null or :leaf2_start <= leaf2.leaf2_name)
and trunk3.trunk3_date between to_date(:date_from, 'MON-YY')
    and last_day(to_date(:date_to, 'MON-YY'))


I have also tried making leaf1 and leaf2 inline views and pre-applying their conditions. Also I have combined leaf1 and trunk1 into one inline view and leaf2 and trunk2 into another inline view and building off of those and nothing even gets close to anything like a reasonable time.

I am fairly sure it is possible to get this running at a more reasonable speed, I have similar queries whose main difference is that they tie to 1 leaf1 or 1 leaf2 and so I am passing in the id and these run in a second or two. And in spite of the fact that the range in theory looks like it has the potential to provide a lot more options, in practice the range is typically used to narrow leaf1 or leaf2 down to just a handful of rows

Any ideas would be appreciated and I don't need a whole complete solution or anything just ideas on what direction I could go would be helpful at this point. I am hoping for a way to do this without adding any new indexes to the tables mainly because in reality these are Oracle Application tables and any change we make there is susceptible to overwrite by Oracle which would 'break' the whole thing at a time when we might not remember exactly what we did to 'fix' it in the first place.

Thanks,
Andrew

And for anyone who happens to be an Oracle Grants Customer and feels like playing around these tables map to Oracle tables in this way:

leaf1 = pa_projects_all
leaf2 = gms_awards_all
trunk1 = pa_expenditure_items_all
trunk2 = gms_award_distributions
trunk3 = pa_cost_distribution_lines_all
Re: Optimizing query with alpanumeric range condition [message #209154 is a reply to message #209144] Wed, 13 December 2006 09:19 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
a) Make sure all the tables are analyzed properly.

b) Get query plans/SQL*Trace files to find out how Oracle is doing the query.

c) How selective is the join from TRUNK2 to TRUNK3 ? I notice TRUNK2 does not have a unique index, so maybe you are picking up more rows than you intend. Also, there is no index on TRUNK3 on the columns used to join it to TRUNK2 - trunk1_id and trunk3_line.
Re: Optimizing query with alpanumeric range condition [message #209156 is a reply to message #209144] Wed, 13 December 2006 09:23 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Can you post the Explain Plan, so we can see what's going on under the bonnet.

Try composite indexes on (Leaf1_id,leaf1_name) and (leaf2_id,leaf2_name), as these should allow you to get index only access to the two leaf tables.

I can't be sure without the Plan, but I'd guess that it picks Trunk3 as the driving table, so try making sure that the other tables can be accessed from this table - an index on trunk2(trunk3_line,trunk2_con2,leaf2_id) for example should enable and Index range scan with no table access on Trunk2.
Re: Optimizing query with alpanumeric range condition [message #209159 is a reply to message #209156] Wed, 13 December 2006 09:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Out of curiosity, how many rows does this return, and how long is 'Intolerably long'
Re: Optimizing query with alpanumeric range condition [message #209161 is a reply to message #209154] Wed, 13 December 2006 09:30 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
a) The tables are analyzed as of Saturday, they will be analyzed again tonight.

b) Working on that, had it at home last night but needed to download SQL Developer to get it.

c) trunk2 to trun 3 is going to be 1 to 1 exactly, trunk2 does actually have a unique index in reality, but it references columns not in this example so I did not include it for simplicity, there is a unique index on trunk3 on both of those columns
"create unique index trunk3_u1 on trunk3(trunk1_id, trunk3_line)"

Re: Optimizing query with alpanumeric range condition [message #209162 is a reply to message #209144] Wed, 13 December 2006 09:35 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
I meant you might need one going the other way i.e. an index on trunk2 (trunk1_id, trunk3_line). The way Oracle will see the query is that it is one to many from trunk1 to trunk3 and then one to many from trunk3 to trunk2. Therefore, if a nested loops type of plan is used, trunk2 will be last to be accessed and an index would help. I know trunk2 and trunk3 are meant to be one to one, but Oracle won't know this from the indexes.
Re: Optimizing query with alpanumeric range condition [message #209170 is a reply to message #209144] Wed, 13 December 2006 10:28 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
Wow kind of overwhelmed by the response for a minute, so trying to address everyone suggestions and questions.


I added the index on trunk2

create index trunk2_n3 on trunk2(trunk1_id, trunk3_line)

This brings my execution time up to about 47 seconds, I had been in the several minute range so this is an improvement.

As for the number of rows returned it very much depends on the parameters passed in, for typical usage I would expect a few thousand rows say 5000 as a ballpark, but there is no saying it won't be run without reasonable bounds would would mean all 880,000 or so rows would come back.

Now the Explain Plane, had to type is because obviously I am translating from what exists in the system to my example
line formate is. All assumes SAMPLE is my schema

Operation-optimizer-cost-cardinality-bytes

SELECT STATEMENT-CHOOSE-756-1-82
   FILTER----
      NESTED LOOPS--756-1-82
         NESTED LOOPS--755-1-77
            HASH JOIN--752-3-183
               TABLE ACCESS(BY INDEX ROWID) SAMPLE.TRUNK3-ANALYZED-211-1403-25254
                  INDEX(RANGE SCAN)SAMPLE.TRUNK3_N2-ANALYZED-24-1-
               HASH JOIN--540-2178-93654
                  TABLE ACCESS(FULL) SAMPLE.LEAF2-ANALYZED-35-41-779
                  TABLE ACCESS(BY INDEX ROWID) SAMPLE.TRUNK2-ANALYZED-504-86926-2086464
                     INDEX(FULL SCAN) SAMPLE.TRUNK2_N1--26-177-
            TABLE ACCESS(BY INDEX ROWID)SAMPLE.LEAF1-ANALYZED-1-1-16
               INDEX(UNIQUE SCAN)SAMPLE.LEAF1_U1
         INDEX(UNIQUE SCAN)SAMPLE.TRUNK1_U1-ANALYZED-1-1-5


I have not done much (just about none) work using the explain plan, so forgive me for sounding stupid, but the costs went way down when I added the new index mentions above, but this index is not actually being used it would seem, doesn't it make since that it would be used to link trunk2 and trunk3?

Andrew
Re: Optimizing query with alpanumeric range condition [message #209174 is a reply to message #209170] Wed, 13 December 2006 10:46 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
From JROW added composite indexes to leaf1 and leaf2.

Helped a bit, in the 30s now explain plan follows:

SELECT STATEMENT-CHOOSE-745-1-82
   FILTER----
      NESTED LOOPS--747-1-82
         NESTED LOOPS--746-1-77
            HASH JOIN--743-3-183
               TABLE ACCESS(BY INDEX ROWID) SAMPLE.TRUNK3-ANALYZED-211-1403-25254
                  INDEX(RANGE SCAN)SAMPLE.TRUNK3_N2-ANALYZED-24-1-
               HASH JOIN--531-2178-93654
                  INDEX(FULL SCAN) SAMPLE.LEAF2_U3--26-41-779
                  TABLE ACCESS(BY INDEX ROWID) SAMPLE.TRUNK2-ANALYZED-504-86926-2086464
                     INDEX(FULL SCAN) SAMPLE.LEAF2_U3
            TABLE ACCESS(BY INDEX ROWID)SAMPLE.LEAF1-ANALYZED-1-1-16
               INDEX(UNIQUE SCAN)SAMPLE.LEAF1_U3
         INDEX(UNIQUE SCAN)SAMPLE.TRUNK1_U1-ANALYZED-1-1-5
Re: Optimizing query with alpanumeric range condition [message #209175 is a reply to message #209174] Wed, 13 December 2006 10:47 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
Oh the indexes are the leafex_u3 in the explain plan

create unique index leaf1_u3 on leaf1(leaf1_id, leaf1_name)
create unique index leaf2_u3 on leaf2(leaf2_id, leaf2_name)
Re: Optimizing query with alpanumeric range condition [message #209176 is a reply to message #209174] Wed, 13 December 2006 10:51 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
Mistake on explain plan, corrected here

SELECT STATEMENT-CHOOSE-745-1-82
   FILTER----
      NESTED LOOPS--747-1-82
         NESTED LOOPS--746-1-77
            HASH JOIN--743-3-183
               TABLE ACCESS(BY INDEX ROWID) SAMPLE.TRUNK3-ANALYZED-211-1403-25254
                  INDEX(RANGE SCAN)SAMPLE.TRUNK3_N2-ANALYZED-24-1-
               HASH JOIN--531-2178-93654
                  INDEX(FULL SCAN) SAMPLE.LEAF2_U3--26-41-779
                  TABLE ACCESS(BY INDEX ROWID) SAMPLE.TRUNK2-ANALYZED-504-86926-2086464
                     INDEX(FULL SCAN) SAMPLE.TRUNK_N3
            TABLE ACCESS(BY INDEX ROWID)SAMPLE.LEAF1-ANALYZED-1-1-16
               INDEX(UNIQUE SCAN)SAMPLE.LEAF1_U3
         INDEX(UNIQUE SCAN)SAMPLE.TRUNK1_U1-ANALYZED-1-1-5
Re: Optimizing query with alpanumeric range condition [message #209179 is a reply to message #209176] Wed, 13 December 2006 11:05 Go to previous message
annagel
Messages: 220
Registered: April 2006
Senior Member
Got it,
create index trunk3_n3 on trunk3(trunk1_id, trunk3_row, trunk3_con1, trunk3_date)

Now table access on trunk3 is gone.

exec time 2.2 seconds
Previous Topic: Hierarcial retrieval - Connect By, Prior Not working
Next Topic: ORA-02019 while accesing DB B from DB A through a dblink.
Goto Forum:
  


Current Time: Fri Dec 02 16:46:55 CST 2016

Total time taken to generate the page: 0.33254 seconds