Query help (was: Need help on the situation I faced here...)

Dev Shed Forums Sponsor:

July 6th, 2006, 08:03 AM

Shinichi

Registered User

Join Date: Jul 2006

Posts: 16

Time spent in forums: 1 h 6 m
Reputation Power: 0

Query help (was: Need help on the situation I faced here...)

Hi all, Im an Oracle newbie here. I have questions here that need guidance from Oracle Expert here. Here it goes:

- there are 3 databases that will be used: tableA (2000 records), tableAsub (45000 records), and tableBsub (850000 records)

- relationship between the 3 tables: "tableA" --link to--> "tableAsub" --link to--> "tableBsub"

- get the minimum "seq" from tableBsub

- get the first "startTime" and last "endTime" from tableBsub, group by an id

- if tableAsub's status is "1" and/or "2", then "period" is based on tableBsub's same id, the last "endTime" will minus with the first "startTime" of tableBsub, and if "tableBsub.endTime" is NULL, then display 0

- if tableAsub's status is "3" to "6", then "period" = sysdate - tableBsub.startTime

Just want to ask experts here can this be done in ONE sql statement using case? Or must use pl/sql's if else and cursor? But my main problem is I'm still fresh and it will take me long time before I'm able to understand Oracle's pl/sql.

Then are there any suggestions to improve the performance of the query? Cause I've done some queries on other situations but they took very long to execute.

Thanks in advance.

ps: Please forgive me for me poor English, do let me know if I do not state my situation clear enough and I'll try to edit it.

Last edited by pabloj : July 6th, 2006 at 09:51 AM.

July 6th, 2006, 10:42 AM

jim mcnamara

......@.........

Join Date: Jun 2004

Posts: 1,308

Time spent in forums: 1 Week 3 Days 6 h 19 m 24 sec
Reputation Power: 48

First off, you need to find out if you have indexes on the foreign keys in your sub tables.

Code:

set long 40
SET PAGES 55
SET LINES 128
spool t.lis
SELECT 
 INDEX_NAME  ,
 TABLE_OWNER ,       
 TABLE_NAME  ,       
 COLUMN_NAME        
 FROM ALL_IND_COLUMNS WHERE
 TABLE_NAME IN ( 'TABLEA', 'TABLEASUB', 'TABLEBSUB')
 ORDER BY TABLE_NAME, COLUMN_NAME;
SPOOL OFF 
SET LINES 80

All of your joins (where TABLEBSUB.fld=TABLEASUB.fld) on the "child" table (in this case fld in TABLEBSUB) have to be indexed if you want to have your query run well.
Get your DBA to help you with indexes. The DBA probably has separate tablespaces for indexes....

Next, you need to read about EXPLAIN PLAN. Read this:
http://www.evolt.org/article/Use_Or...ueries/17/2986/

Now, you are ready to roll.

July 10th, 2006, 05:42 AM

Shinichi

Registered User

Join Date: Jul 2006

Posts: 16

Time spent in forums: 1 h 6 m
Reputation Power: 0

Quote:

Originally Posted by jim mcnamara

First off, you need to find out if you have indexes on the foreign keys in your sub tables.

Code:

set long 40
SET PAGES 55
SET LINES 128
spool t.lis
SELECT 
 INDEX_NAME  ,
 TABLE_OWNER ,       
 TABLE_NAME  ,       
 COLUMN_NAME        
 FROM ALL_IND_COLUMNS WHERE
 TABLE_NAME IN ( 'TABLEA', 'TABLEASUB', 'TABLEBSUB')
 ORDER BY TABLE_NAME, COLUMN_NAME;
SPOOL OFF 
SET LINES 80

Hi jim mcnamara, thank you very much for your reply. I'll try to look out for the index thingy you mentioned and maybe seek my senior's help. If I still face any problem I'll post here again and seek for guidance.

Thanks very much

July 11th, 2006, 11:16 AM

Shinichi

Registered User

Join Date: Jul 2006

Posts: 16

Time spent in forums: 1 h 6 m
Reputation Power: 0

Code:

select tbl.clsCode	clsCode,
	   tbl.mod		mod,
	   tbl.job		job,
	   tbl.firstTransTime firstTransTime,
	   tbl.lastTransTime lastTransTime,
	   floor((sysdate - tbl.firstTransTime)*24) || 'hrs '
			|| mod(floor((sysdate - tbl.firstTransTime)*24*60), 60) || 'mins' cycleTime
from	(select dbiscs.tbl_mes_lotmaster.CLASSCODE			clsCode,
			    dbiscs.tbl_mes_lotmaster.PRIMARYITEMDESC	mod,
			    lts.WIPENTITYNAMESUB						job,
			    lts.CHECKINTIME								firstTransTime,
			    lts.CHECKOUTTIME							lastTransTime
		from   dbiscs.tbl_mes_lotmaster,
			   dbiscs.tbl_mes_lotmastersub,
			   (select lts1.wipentityid wipentityid,
			   		   lts1.wipentityname wipentityname,
					   lts1.wipentitynamesub wipentitynamesub,
					   lts1.checkintime checkintime,
					   lts2.checkouttime checkouttime
			   from tbl_mes_lottracksub lts1,
			   		(select wipentitynamesub, min(operationseq) as operationseq, max(checkouttime) as checkouttime
					from tbl_mes_lottracksub
					group by wipentitynamesub) lts2
			   where lts1.wipentitynamesub = lts2.wipentitynamesub
			   and lts1.operationseq = lts2.operationseq) lts
		where  dbiscs.tbl_mes_lotmaster.ORGANIZATIONID = '18'
		and	   dbiscs.tbl_mes_lotmastersub.LOTSTATUS like '%%'
		and	   dbiscs.tbl_mes_lotmaster.CLASSCODE = 'M10-WIP1'
		and	   dbiscs.tbl_mes_lotmaster.PRIMARYITEMDESC like '%%'
		and	   lts.WIPENTITYNAMESUB like '%%'
		AND	   (TO_CHAR(dbiscs.TBL_MES_LOTMASTER.DATERELEASED, 'YYYYMMDDHH24Mi') BETWEEN '000101010000' AND '999912312359')
		AND	   (TO_CHAR(dbiscs.TBL_MES_LOTMASTER.SCHCOMPLETIONDATE, 'YYYYMMDDHH24Mi') BETWEEN '000101010000' AND '999912312359')
		and	   dbiscs.tbl_mes_lotmaster.WIPENTITYID = dbiscs.tbl_mes_lotmastersub.WIPENTITYID
		and	   dbiscs.tbl_mes_lotmaster.CLASSCODE = dbiscs.tbl_mes_lotmastersub.CLASSCODE
		and	   dbiscs.tbl_mes_lotmastersub.WIPENTITYID = lts.WIPENTITYID
		and	   dbiscs.tbl_mes_lotmastersub.WIPENTITYNAME = lts.WIPENTITYNAME
		and	   dbiscs.tbl_mes_lotmastersub.WIPENTITYNAMESUB = lts.WIPENTITYNAMESUB) tbl
where	tbl.firstTransTime is not null

Hi guys, just wanna ask if anyone here know how to make this query runs faster? It took about 2 minutes for this query to run and display the result recordsets. Should I use cursor? Or union? Any suggestions and guidance are welcome.

Thanks in advance.

July 11th, 2006, 11:20 AM

pabloj

Modding: Oracle MsSQL Firebird

Dev Shed God 7th Plane (8000 - 8499 posts)

Join Date: Jun 2001

Location: Outside US

Posts: 8,042

Time spent in forums: 2 Months 3 Weeks 6 Days 14 h 44 m 37 sec
Reputation Power: 281

Pretty standard answer, check it's explain plan and see if it needs further indexing.

__________________
My blog
Tutorials about OSS databases, DBMonster ...
Contribute to OSS Development, fill bug reports!
Developer Shed eSupport
Commented my.ini/my.cnf (ADD YOUR OWN CONFIG TRICK)
An introduction to database normalization
Natural or Surrogate key
Custom ordering for your results
Correlated and uncorrelated subqueries
Don't turn your outer joins into inner joins
Random data (with a bias)

July 11th, 2006, 06:50 PM

Shinichi

Registered User

Join Date: Jul 2006

Posts: 16

Time spent in forums: 1 h 6 m
Reputation Power: 0

Quote:

Originally Posted by pabloj

Pretty standard answer, check it's explain plan and see if it needs further indexing.

Hi pablo, thanks for your reply. Will check it out later. But what if indexing already was done and it still took around 2 minutes to run the above query, any other way to solve it other than indexing?

Thanks

Update: Anyone know how to solve the "TABLE ACCESS FULL" problem? Thanks.

July 12th, 2006, 05:31 AM

pabloj

Modding: Oracle MsSQL Firebird

Join Date: Jun 2001

Location: Outside US

Posts: 8,042

Time spent in forums: 2 Months 3 Weeks 6 Days 14 h 44 m 37 sec
Reputation Power: 281

Quote:

Originally Posted by Shinichi

Update: Anyone know how to solve the "TABLE ACCESS FULL" problem? Thanks.

That's what everyone wants to know ...

but there is no easy answer, you can force usage of index with hints or by altering some server parameters, but consider that if a value is matched by a large percentage of total rows it is better to do a table scan than an index scan + a table scan (because index is useful to locate records, but you need to access the table to retrieve them.
Note that fresh stats are very helpful in choosing the best execution plan for a query.
If you need further assistance you should post your table structure, the query and it's explain plan.

July 12th, 2006, 09:54 AM

Shinichi

Registered User

Join Date: Jul 2006

Posts: 16

Time spent in forums: 1 h 6 m
Reputation Power: 0

Quote:

Originally Posted by pabloj

That's what everyone wants to know ...

Hi, thanks for your reply again pablo

Hmmm guess I need to try out to solve my problem first. If I still face any difficulties then I'll post up the table structure, query and explain plan to seek guidance.

Thanks

July 12th, 2006, 12:53 PM

clivew

Contributing User

Join Date: Jan 2006

Location: Carlsbad, CA

Posts: 852

Time spent in forums: 4 Days 10 h 56 m 22 sec
Reputation Power: 19

First, the complexity is such that a PL/SQL approach may be quicker.

Some specific observations that may or may not help.

The final line:

Code:

where	tbl.firstTransTime is not null

This is so broad I believe it guarantees a full table scan. NULLs are never included in indexes so I do not think the optimizer would know how many firstTranTimes were NULL. Someone else may know better<?>

It will also be hard to use indexes where so many items in the where clause are Like with wild cards.
Similarly, the BETWEEN clauses cover a wide range of values.

To sum up, your where clauses appear to cover too wide a range of possibilities for indexes to help a lot.

See if there is anything you can do to reduce the size of the set of records returned by the driving table.

Clive

#10

July 12th, 2006, 06:52 PM

Shinichi

Registered User

Join Date: Jul 2006

Posts: 16

Time spent in forums: 1 h 6 m
Reputation Power: 0

Quote:

Originally Posted by clivew

First, the complexity is such that a PL/SQL approach may be quicker.

Some specific observations that may or may not help.

The final line:

Code:

where	tbl.firstTransTime is not null

Hi, thanks Clive, so by PL/SQL you mean I should use cursors and such? And for the where clauses cannot be "changed" further due to the business requirements(I need to use LIKE and BETWEEN to compare value passed from the browser). So any other ways to do it besides PL/SQL or changing the where clauses?

Thanks again

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: