| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: update performance
-----Original Message-----
From: sujit_at_oil.asm.nic.in [mailto:sujit_at_oil.asm.nic.in]
Sent: Tuesday, January 16, 2001 8:56 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: update performance
Hi KP;
Please ANALYZE your tables and run SQL.
SA
I ahve a requirement to update a table using a joing - details: Table A: (colA, colB,...): 2 million rows Table B: (colA, colC,...): 2 milion rows
update cmd:
update tableA A
set colA = (select B.colC fdrom tableB B where B.tableB.colA = A.tableA.colA
)
I tried it in PL/SQL and as the straight SQL but it takes FOREVER without
coming back...
should I be doiing something better?
(I have an index on the two columns on TableB)
Thanks
-Krishna.
-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Krishna Prasad
Sent: Monday, January 08, 2001 8:41 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Btmap Index
Thanks - I'll try the Hint - the table has 2 million rows (with about 600 distinct values for this column)
-krishna.
-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Kevin Kostyszyn
Sent: Monday, January 08, 2001 3:50 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: Btmap Index
I ran into a similar problem a while back. If the database is set to use
the CBO, it might not use the index. Someone on this list gave a great
reason why, perhaps the table is just very small and the CBO just decided to
do a full table scan instead. You could use a hint and tell it to use rule
based on that query and it will use the index on the where clause. That's
what I did. The question is, how big is the table and how much data does it
have in it? If it is not a lot, there really is nothing to be concerned
about. Oh yeah, and almost 100% positive that there is no setting in the
init file for the index.
Sincerely,
Kevin
Hi,
Is there any init.ora parameter that needs to be turned to activate bitmap
indexes?
I have a bitmap index on one column C1, but when i examine the explain pan
of the
query: " select C1, b, c from table TT where C1 in ('dd','ff')" , it looks
like it is not using
the index but doing a full table scan...
thanks
-krishna
-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Jacques
Kilchoer
Sent: Monday, January 08, 2001 2:01 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Date comparison question
> -----Original Message-----
> From: Lisa Yates [ mailto:cosnit_at_creighton.edu
<mailto:cosnit_at_creighton.edu> ]
> Sent: lundi, 8. janvier 2001 11:27
>
> Why doesn't this query ever return...
>
> where run_date = to_date('01-03-2001 16:34:59','mm-dd-yyyy
> hh24:mi:ss')
>
> but this query does....
>
> where to_char(run_date) = to_char(to_date('01-03-2001
> 16:34:59','mm-dd-yyyy hh24:mi:ss'))
>
> and so does this query....
>
> where to_char(run_date,'mm-dd-yyyy hh24:mi:ss') = '01-03-2001
> 16:34:59'
Well, I guess my suggestion that run_date may contain a "BCE" (before common era) date was not the answer to your problem. Maybe I misunderstood the issue. When you say "why doesn't the query ever return", do you mean it returns zero rows, or do you mean that the query is taking an abnormally long time? If it's taking an abnormally long time, an explain plan on the two queries may reveal the cause.
Jacques R. Kilchoër
(949) 754-8816
Quest Software, Inc.
8001 Irvine Center Drive
Irvine, California 92618
U.S.A.
http://www.quest.com <http://www.quest.com>
"WorldSecure Server <baking.bestfoods.com>" made the following annotations on 01/16/01 21:57:09
"WorldSecure Server <baking.bestfoods.com>" made the following annotations on 01/23/01 10:54:51
 |
 |