Home » SQL & PL/SQL » SQL & PL/SQL » Table Determination
Table Determination [message #333233] Thu, 10 July 2008 23:54 Go to next message
balaji1482
Messages: 312
Registered: July 2007
Location: INDIA
Senior Member

hi guys,


can anybody tell me how to determine a small table or large table? because based upon the table size only we create index for such tables which goes for full table scan.

[Updated on: Thu, 10 July 2008 23:54]

Report message to a moderator

Re: Table Determination [message #333235 is a reply to message #333233] Thu, 10 July 2008 23:57 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guideline as stated above

balaji1482 wrote on Thu, 10 July 2008 21:54
hi guys,
can anybody tell me how to determine a small table or large table? because based upon the table size only we create index for such tables which goes for full table scan.


Small table has short name & Large table has BIG name obviously!

[Updated on: Thu, 10 July 2008 23:58] by Moderator

Report message to a moderator

Re: Table Determination [message #333239 is a reply to message #333233] Fri, 11 July 2008 00:07 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> how to determine a small table or large table?
If your statististics is up-to date, use NUM_ROWS value from USER_TABLES.
Alternatively you may aggregate BYTES value from USER_SEGMENTS or USER_EXTENTS.

> because based upon the table size only we create index for such tables which goes for full table scan.
May I know how are you going to determine the indexed column(s)?
Re: Table Determination [message #333243 is a reply to message #333239] Fri, 11 July 2008 00:21 Go to previous messageGo to next message
balaji1482
Messages: 312
Registered: July 2007
Location: INDIA
Senior Member

one can determine the indexed colmn using view called user_ind_columns
Re: Table Determination [message #333245 is a reply to message #333233] Fri, 11 July 2008 00:26 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> because based upon the table size only we create index for such tables which goes for full table scan.
> one can determine the indexed colmn using view called user_ind_columns
Yes, but the index must already exist. It will contain nothing before you create it.
Maybe I should use 'decide' instead of 'determine'.
Re: Table Determination [message #333251 is a reply to message #333245] Fri, 11 July 2008 00:44 Go to previous messageGo to next message
balaji1482
Messages: 312
Registered: July 2007
Location: INDIA
Senior Member

"May I know how are you going to determine the indexed column(s)? "

Using WHERE clause specified in the query?

[Updated on: Fri, 11 July 2008 00:46]

Report message to a moderator

Re: Table Determination [message #333252 is a reply to message #333251] Fri, 11 July 2008 00:46 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
balaji1482 wrote on Thu, 10 July 2008 22:44
"May I know how are you going to determine the indexed column(s)? "

using WHERE clause?


how/where are you going to isolate all the WHERE clause?
Re: Table Determination [message #333256 is a reply to message #333252] Fri, 11 July 2008 01:01 Go to previous messageGo to next message
balaji1482
Messages: 312
Registered: July 2007
Location: INDIA
Senior Member

im not sure.if u know please let me know the answer .
Re: Table Determination [message #333259 is a reply to message #333256] Fri, 11 July 2008 01:04 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
balaji1482 wrote on Thu, 10 July 2008 23:01
im not sure.if u know please let me know the answer .


the answer of what?
Re: Table Determination [message #333308 is a reply to message #333233] Fri, 11 July 2008 03:42 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> im not sure.if u know please let me know the answer .
I do not know too. It was your idea to create indexes on large tables. I was just curious for the reasons as you started wrong way.
Normally, you do not create indexes just because the table is "big". Normally you create data model based on application requirements (within report queries). This model may contain indexes, when there is need to filter certain column (WHERE clause). But, sometimes, primary (or unique) key may suffice even for big tables.
Previous Topic: find version number of oracle
Next Topic: How to split a row having a delimited string as one of the columns into multiple rows
Goto Forum:
  


Current Time: Fri Dec 09 21:14:50 CST 2016

Total time taken to generate the page: 0.05910 seconds