OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Full Table Scan

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Full Table Scan [message #132042]

Wed, 10 August 2005 09:02

puneetsachar
Messages: 97
Registered: March 2005
Location: New Delhi, India

Member

Hi I have one SQL qurery and infact 7 same SQL Query Like this aswell and the problem is the query is taking FULL table Scan

INSERT INTO load_pdr_group (
business_org
, baes_site
, emp_cat
, baes_grade
, baes_job_code
, baes_exec_marker
, employee_catg
, yesno
, baes_overtime_paid
, baes_pdr_group
, pdr_group_id
)
SELECT x.business_org
, x.baes_site
, x.emp_cat
, x.baes_grade
, x.baes_job_code
, x.baes_exec_marker
, x.employee_catg
, x.yesno
, x.baes_overtime_paid
, x.baes_pdr_group
, x.pdr_group_id
FROM (SELECT apg.business_org
, apg.baes_site
, apg.emp_cat
, apg.baes_grade
, apg.baes_job_code
, apg.baes_exec_marker
, apg.employee_catg
, apg.yesno
, apg.baes_overtime_paid
, apg.baes_pdr_group
, apg.pdr_group_id
, NVL(apg.exception,'X') exception
, NVL(apg.deleted, 'X') deleted
, MAX(apg.pdr_group_id)
OVER (PARTITION BY apg.business_org
, apg.baes_site
, apg.emp_cat
, apg.baes_grade
, apg.baes_job_code
, apg.baes_exec_marker
, apg.employee_catg
, apg.yesno
, apg.baes_overtime_paid
, apg.baes_pdr_group
, apg.pdr_group_id) max_pdr_group_id
FROM audit_pdr_group apg) x
WHERE x.pdr_group_id = x.max_pdr_group_id
AND x.exception != 'Y'
AND x.deleted != 'Y'

I have Primary key on the pdr_group_id

Tell me what all index I will make on this.. so that I'm able to Use Index

My DB is 8.1.7.4

Please help me....

Puneet Sachar

Report message to a moderator

Re: Full Table Scan [message #132052 is a reply to message #132042]

Wed, 10 August 2005 10:21

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

An FTS is not always bad.
Without knowledge on distribution of data or any execution plan/ statspack report/ trace analysis we have no idea what might be happening actually ( assuming the tables/indexes are analyzed and CBO is using those indexes).

Report message to a moderator

Re: Full Table Scan [message #132078 is a reply to message #132042]

Wed, 10 August 2005 14:20

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

Ah, I remember you.

How long does the INSERT take to run now?

Like Mahesh Rajendran suggests, sometimes a full table scan is the fastest, most efficient path to the data.

Just out of curiosity, what are the results of the following queries?

SET NULL [NULL]
SELECT   apg.exception
,        COUNT(*)
FROM     audit_pdr_group apg
GROUP BY apg.exception
ORDER BY apg.exception
/
SELECT   apg.deleted
,        COUNT(*)
FROM     audit_pdr_group apg
GROUP BY apg.deleted
ORDER BY apg.deleted
/

Report message to a moderator

Re: Full Table Scan [message #132162 is a reply to message #132078]

Thu, 11 August 2005 03:35

puneetsachar
Messages: 97
Registered: March 2005
Location: New Delhi, India

Member

Yeah, its same your quesry...

but I'm not geting the same data means.. might be missing something in it..

But this quesry have give me chnace to look more in table scan issue which I know i can never understand.. don
't know why

Anyways u have asked the to run two query here is the answer

SELECT apg.exception
, COUNT(*)
FROM audit_pdr_group apg
GROUP BY apg.exception
ORDER BY apg.exception

---> Exception Count(*)
--------------- ------------
Y 44656
NULL 1916626

(FULL TABLE ACCESS)
OPTIMISER MODE = CHOOSE

(2) SELECT apg.deleted
, COUNT(*)
FROM audit_pdr_group apg
GROUP BY apg.deleted
ORDER BY apg.deleted

---> DELETED Count(*)
--------------- ------------
Y 581259
NULL 1380023

FULL TABLE ACCESS)
OPTIMISER MODE = CHOOSE

Report message to a moderator

Previous Topic:	All sorting etc done on temp space
Next Topic:	how to get full version of sql statement

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 16:29:08 CST 2024