1. Dial 1-0-0-4-6.
2. Look at the latching and pinning statistics promoted at asktom, ixora,
and jlcomp.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Hotsos Clinic 101 in Chicago, London, Reykjavik, Ottawa, Denver
- Visit www.hotsos.com for schedule details...
I wrote an LIO monitor, that should help to show what kind of LIO you are
doing. It could help explain the difference in time. Give it a try and let
me know what you see. Download it from oraperf.com
Anjo.
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Cary, Denny - Thanks very much. I was running out of ideas on a Friday
afternoon, then I just ran out. Have a good weekend, will try these
suggestions Monday.
Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Friday, April 04, 2003 3:54 PM
Of course it will do full table scans! You're not limiting your join
to any set of values, you are trying to read two whole tables. Because
of the multiblock reads, full table scans and hash join will be more
efficient
then a nested loops plan where one table is read sequentially and the other
Title: RE: Full table scan
Venu,
How may records? If it's just a few hundred, a full table scan might be faster.
Throw a RULE hint at it and see what happens then.
What version of Oracle? CBO in 7.3.4 wasn't all that smart.
Jerry Whittle
ASIFICS DBA
NCI Information Systems Inc
At 09:08 PM 2/4/2003, you wrote:
I'm doing a select from 2 tables joining them using a primary key- ref
key on the tables and the explain plan is showing 2 full table scans...
Even after analyzing the tables and the Optimizer mode is set to CHOOSE.
SELECT a.col1, b.col2
FROM table1 a, table2
Hi Venu,
Is it a.col1 as primary key of table1 and b.col1 as primary key of table2 ?
if yes try this,
select /*+ FIRST_ROWS */
a.col1, b.col2
from table1 a, table2 b
where a.col1 = b.col1
Rgrds,
Sony
-Original Message-
From: Venu Gopal Andem [SMTP:[EMAIL PROTECTED]]
Use Excel.
K. ;O)
-Original Message-
Sent: 07 January 2003 16:29
To: Multiple recipients of list ORACLE-L
Hi
How to avoid FULL TABLE SCAN?
Thx
-seema
_
MSN 8 with e-mail virus protection service: 2 months FREE*
I think you are new to the list.
All '_' parameters given as a solution without a warning that their use can
be dangerous, are meant as fun and nothing else.
Regards
Naveen
-Original Message-
Sent: Wednesday, January 08, 2003 8:44 AM
To: Multiple recipients of list ORACLE-L
Is it good
Title: RE: FULL TABLE SCAN?
Don't use tables, just use views ... view scans are better and faster, because by definition views are just definitions, so it will be faster. Memory scans are faster than disk access by a factor of (say) 10,000 (if you believe manufacturer's claims).
8:)
Raj
Title: RE: FULL TABLE SCAN?
_full_table_scan=FALSE
-Original Message-
From: Seema Singh [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 07, 2003 11:29 AM
To: Multiple recipients of list ORACLE-L
Subject: FULL TABLE SCAN?
Hi
How to avoid FULL TABLE SCAN?
Thx
-seema
1. Set optimizer_mode to RULE.
2. Make sure all statements have a WHERE clause.
3. Dont use functions in the equality clauses.
4. Create an index on each and every column you have in the database.
Take my advice. I dont use it anyway :)))
Raj
At great personal risk, I will tell you some secrets.
There are many options.
1) Don't query data. To really enforce this, remove select, insert, update
and delete privileges from all users.
2) If you must query data, use an index and only an index. Create indexes
that cover all possible
Title: RE: FULL TABLE SCAN?
A Where clause in your SQL and indexes that support the Where clause are a good place to start.
Jerry Whittle
ASIFICS DBA
NCI Information Systems Inc.
[EMAIL PROTECTED]
618-622-4145
-Original Message-
From: Seema Singh [SMTP:[EMAIL PROTECTED]]
Hi
Create a meaningful index and keep your stats up to date.
Dave
The OT list rules
-Original Message-
Sent: Tuesday, January 07, 2003 10:29 AM
To: Multiple recipients of list ORACLE-L
Hi
How to avoid FULL TABLE SCAN?
Thx
-seema
Seema:
Use an index, obviously. If and index exists, ensure that the query uses
it. The select columns order should match the order of the index columns.
Or try using a HINT.
Sometimes a full scan is not a bad thing. Are there less than 100,000 rows
in the table? Sometimes the optimizer will
Title: RE: FULL TABLE SCAN?
It's a
bit buggy in 8i!
-Original Message-From: Deshpande, Kirti
[mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 07, 2003
1:09 PMTo: Multiple recipients of list ORACLE-LSubject:
RE: FULL TABLE SCAN?
Is
that backported to 8i and 7.3
Is it good to use this (_full_table_scan=FALSE)?
If there are less than (say about 1 lakh records), a full scan of
table is better. How efficient is the usage of the above by
setting it to false.
Rgds,
Satya
On Tue, 07 Jan 2003 Koivu, Lisa wrote :
_full_table_scan=FALSE
-Original
Title: RE: FULL TABLE SCAN?
Is
that backported to 8i and 7.3 ??? :)
;)
Thanks.
-
Kirti
-Original Message-From: Koivu, Lisa
[mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 07, 2003
11:05 AMTo: Multiple recipients of list ORACLE-LSubject:
RE: FULL TABLE SCAN?
_full_table_scan
You have a outer join in yr history table. Also you have a IN condition for
the history table for which the CBO might think a FTS might be cheaper..
Babu
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, January 03, 2003 7:19 AM
Hi List,
I
2 things:
1) don't send attachments to the list, they rarely
arrive unscathed. Yours didn't.
2) why do you think the full table scan is an 'error'?
It could be that an FTS is the fastest access method
for this query.
3) ok, three things. Please make an attempt to format
longish queries so
Personally, I always format queries before working
with
them, otherwise it is too difficult to determine
what is
being done in the query.
Jared
Glad to see a fellow maniac :-).
Regards,
Stephane Faroult
Oriole
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
Yea same here, column names, table names all on separate lines :)
joe
Personally, I always format queries before working
with
them, otherwise it is too difficult to determine
what is
being done in the query.
Jared
Glad to see a fellow maniac :-).
Regards,
Stephane Faroult
And people get so pissed when you take the time to do this while they are
standing over your shoulder... I take a perverse pleasure in making them
wait... While we are on the subject... I also get annoyed by mixed case...
I hate the queries that look like...
SELECT *
from MY_table_NAME a,
ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: Re: Full table scan error
And people get so pissed when you take the time to do this while they are
standing over your shoulder... I take a perverse pleasure in making them
wait... While we are on the subject... I also get annoyed
Hi Ethan,
If selectivity is that low, try using a bitmapped index.
HTH, Remco
-Oorspronkelijk bericht-
Van: Post, Ethan [mailto:[EMAIL PROTECTED]]
Verzonden: vrijdag 8 juni 2001 2:26
Aan: Multiple recipients of list ORACLE-L
Onderwerp: Full Table Scan and TKPROF Output
My
26 matches
Mail list logo
