Home » RDBMS Server » Performance Tuning » Full Table Scan
Full Table Scan [message #132042] Wed, 10 August 2005 09:02 Go to next message
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
Re: Full Table Scan [message #132052 is a reply to message #132042] Wed, 10 August 2005 10:21 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
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).

Re: Full Table Scan [message #132078 is a reply to message #132042] Wed, 10 August 2005 14:20 Go to previous messageGo to next message
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
/
Re: Full Table Scan [message #132162 is a reply to message #132078] Thu, 11 August 2005 03:35 Go to previous message
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

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


Current Time: Thu Mar 28 13:41:05 CDT 2024