Re: after running a query (count, select...like) i don't get the prompt back

On 7 Aug 2002 13:35:02 -0700, Cihat <c.genc_at_interpay.nl> wrote:
>Hi Richard,
>
>The size isn't that big, it holds 200.000 records and the table size
>is 8,5 MB.
>In the same database I have a larger table and that's working fine.
>It's weird,
>if i do 'select count(*) from cardholder' the problem occurs, but if I
>do
>'select count(*) from cardholder where cardholder_id < 250000' it
>shows the correct total amount of records and doesn't give any
>problems.

I've seen variations on this problem due to constraint problems (missing or invalid, or messed up index) and optimizer problems (in particular, rule-based "choose" system was messed up due to an import of a table that someone had run analyse on).

Also, if you have a lot of deleted records you could be scanning a whole lot of empty blocks or have a high-water problem. Try deleting and recreating the table (or truncating/reloading).

And just for the heck of it, dbverify.

>
>regards,
>Cihat
>
>
>
>Richard Foote <Richard.Foote_at_oracle.com> wrote in message news:<3D50E900.F910A331_at_oracle.com>...
>> Hi Cihat,
>>
>> Do you have any idea on the size of the cardholder table ? If it's
>> large, then it could very well take a considerable amount of time (try
>> as I might, it takes me a while to walk from Canberra to Dubbo).
>>
>> Take a look if you can (or get your DBA to take a look) at
>> v$session_longops, it displays details of long running queries and might
>> give you an indication on how long to go.
>>
>> Cheers
>>
>> Richard
>>
>> Cihat wrote:
>> >
>> > Hi,
>> >
>> > When i'm running a query it doesn't show the result and it also doesn't
>> > go back to the prompt. This only happens in one table in the database.
>> >
>> > The query what i'm running is simple:
>> >
>> > select count(*)
>> > from cardholder
>> >
>> > It also happens when i do a query like this:
>> >
>> > select *
>> > from cardholder
>> > where cardholder_name like 'Cihat%'
>> >
>> > Can anyone advise me about this?
>> >
>> > regards,
>> > Cihat
>> --

jg

-- 
These opinions are my own. 
http://www.garry.to                                       Oracle and unix guy.
mailto:joel-garry_at_nospam.cox.net                       Remove nospam to reply.