Re: Difference - Rebuild and Analyze index
Thanks a lot John, Darshan ad Ora for
clearing my confusions.
I guess now i have a better understanding of which
option to use and when to use.
Thanks buddies,
Marul.
- Original Message -
From:
ora ak
To: Multiple recipients of list ORACLE-L
Sent: Wednesday, October 16, 2002 9:19
PM
Subject: Re: Difference - Rebuild and
Analyze index
Yes Marul , I think there is a big differenece in them . When you are
rebulding the index means you are re-organzing the entries in the index , may
be removing the entries for the rows deleted in past and so forth so on. But
this doesn't generate any statistics about index.
When you analyze index, you generate information ( statistics) about the
data in index , like leaf blocks or depth of index etc.
None of these should require orther , but they have some effect .
Like if you rebuild index , after that you dont have latest statistics and
queries may not perform upto mark .
-oramagic
Marul Mehta <[EMAIL PROTECTED]> wrote:
Hi,
Can anybody please tell me the difference
between -
SQL > execute
DBMS_UTILITY.ANALYZE_SCHEMA('BLAH','COMPUTE',NULL,30,'FOR ALL
INDEXES');
and
SQL > select 'ALTER INDEX ' || INDEX_NAME ||
' REBUILD ONLINE;' from USER_INDEXES
If I execute any one the above do i need to
execute the other also?
After how many days/hour it should be
executed.
TIA,
Marul.
Do you Yahoo!?Faith Hill - Exclusive
Performances, Videos, & morefaith.yahoo.com
Difference - Rebuild and Analyze index
Hi,
Can anybody please tell me the difference between
-
SQL > execute
DBMS_UTILITY.ANALYZE_SCHEMA('BLAH','COMPUTE',NULL,30,'FOR ALL
INDEXES');
and
SQL > select 'ALTER INDEX ' || INDEX_NAME || '
REBUILD ONLINE;' from USER_INDEXES
If I execute any one the above do i need to execute
the other also?
After how many days/hour it should be
executed.
TIA,
Marul.
Re: Inserts are taking time !
PROTECTED] > > > > > > > > > > > > On Wednesday 04 September 2002 04:23, [EMAIL PROTECTED] wrote: > > > > > It sounds to me like the indexes are going into overflow - this will > > > cause > > > > > > > > What do you mean by 'overflow'? > > > > > > > > Jared > > > > > > > > > the insert time to increase. I would suggest batching up the inserts, > > > > > dropping the indexes, running the inserts and re-creating the > >indexes. > > > > > > > > > > Chris > > > > > > > > > > -Original Message- > > > > > Sent: 04 September 2002 07:53 > > > > > To: Multiple recipients of list ORACLE-L > > > > > > > > > > > > > > > Hi All, > > > > > > > > > > We have a table which can contain more than half a million records. > >When > > > > we > > > > > try to insert some 10k records in the empty table it get inserted in > >10 > > > > > min. but as the size increases time taken to insert also increases. > > > After > > > > > 350,000 records it takes around an hour to insert 10k records. > > > > > There are around 15 columns in it out of which 11 are indexed. There > >is > > > > one > > > > > concatenated function-based index on two columns of Varchar type and > >two > > > > > separate index for the same two columns. > > > > > > > > > > I have checked the free space for the tablespaces to which the table > >and > > > > > indexes are attached to. They are in two separate tbs. > > > > > > > > > > Any clues why this is happenning. > > > > > > > > > > TIA > > > > > Marul. > > > > > > > > > > > > Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1" > > > > Content-Transfer-Encoding: 7bit > > > > Content-Description: > > > > > > > > -- > > > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > > > -- > > > > Author: > > > > INET: [EMAIL PROTECTED] > > > > > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > > > San Diego, California-- Public Internet access / Mailing Lists > > > > > > > > To REMOVE yourself from this mailing list, send an E-Mail message > > > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > > > the message BODY, include a line containing: UNSUB ORACLE-L > > > > (or the name of mailing list you want to be removed from). You may > > > > also send the HELP command for other information (like subscribing). > > > > > > > > > > > > > > > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > > -- > > > Author: Marul Mehta > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > > San Diego, California-- Public Internet access / Mailing Lists > > > > > > To REMOVE yourself from this mailing list, send an E-Mail message > > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > > the message BODY, include a line containing: UNSUB ORACLE-L > > > (or the name of mailing list you want to be removed from). You may > > > also send the HELP command for other information (like subscribing). > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > > -- > > > Author: Naveen Nahata > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > > San Diego, California-- Public Internet access / Mailing Lists > > > > > > To REMOVE yourself from this mailing list, send an E-Mail message > > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > > the message BODY, include a line containing: UNSUB ORACLE-L > > >
Re: Inserts are taking time !
Thanks a lot for the response, No its not a batch insert; each insert is done with auto-commit=true from the java application. So, after each insert a commit is done at the Db level, which is the root cause of such a delay, I guess. But if this auto-commit is the issue than why first 10K records are inserted quickly. I cannot disable constraints even for a sinlge second as there will be heavy reads going on even when inserts are taking place. Any clues? TAI Marul. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, September 05, 2002 2:53 PM > Marul, > > I think this question needs to be answered, otherwise impossible to make > suggestions... > > Chris > > -Original Message- > Sent: 05 September 2002 08:18 > To: Multiple recipients of list ORACLE-L > > > Marul, what i fail to understand is: > > Are you running a batch job of inserting 350,000 inserts? > > If that is the case then you should go for dropping and recreating the > indexes. Can you partition the table and use local partitioned indexes? > > Can't you try the option of inserting in parallel? > > Did you try disabling the constraints and then ENABLE NOVALIDATE them(that > will only work if you r sure of the data)? > > Naveen > > -Original Message- > Sent: Thursday, September 05, 2002 11:48 AM > To: Multiple recipients of list ORACLE-L > > > Thanks Chris, > So than any clues how to resolve this issue, as earliest, becuase this is > causing bottleneck in our application . > > Rgds, > Marul. > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Thursday, September 05, 2002 2:29 AM > > > > Good question, Jared! Perhaps 'overflow' is technically not the correct > > term to use to decribe this scenario but it seemed to fit the bill > > sufficiently to mail off a quick one-liner solution without going into > great > > depth. Some of us have work to do, you know ;) > > > > To redeem myself I probably should have mentioned that this table sounds > > pretty volatile. Consequently the index(es) are likely to end up fairly > > disorganized, especially if the 350k records are being inserted in > > ascending order. Once you start adding levels to the index things start > to > > slow down > > > > Chris > > > > -Original Message- > > Sent: 04 September 2002 16:50 > > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > > > > > > On Wednesday 04 September 2002 04:23, [EMAIL PROTECTED] wrote: > > > It sounds to me like the indexes are going into overflow - this will > cause > > > > What do you mean by 'overflow'? > > > > Jared > > > > > the insert time to increase. I would suggest batching up the inserts, > > > dropping the indexes, running the inserts and re-creating the indexes. > > > > > > Chris > > > > > > -Original Message- > > > Sent: 04 September 2002 07:53 > > > To: Multiple recipients of list ORACLE-L > > > > > > > > > Hi All, > > > > > > We have a table which can contain more than half a million records. When > > we > > > try to insert some 10k records in the empty table it get inserted in 10 > > > min. but as the size increases time taken to insert also increases. > After > > > 350,000 records it takes around an hour to insert 10k records. > > > There are around 15 columns in it out of which 11 are indexed. There is > > one > > > concatenated function-based index on two columns of Varchar type and two > > > separate index for the same two columns. > > > > > > I have checked the free space for the tablespaces to which the table and > > > indexes are attached to. They are in two separate tbs. > > > > > > Any clues why this is happenning. > > > > > > TIA > > > Marul. > > > > > > Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1" > > Content-Transfer-Encoding: 7bit > > Content-Description: > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California-- Public Internet access / Mai
Re: Inserts are taking time !
Thanks Chris, So than any clues how to resolve this issue, as earliest, becuase this is causing bottleneck in our application . Rgds, Marul. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, September 05, 2002 2:29 AM > Good question, Jared! Perhaps 'overflow' is technically not the correct > term to use to decribe this scenario but it seemed to fit the bill > sufficiently to mail off a quick one-liner solution without going into great > depth. Some of us have work to do, you know ;) > > To redeem myself I probably should have mentioned that this table sounds > pretty volatile. Consequently the index(es) are likely to end up fairly > disorganized, especially if the 350k records are being inserted in > ascending order. Once you start adding levels to the index things start to > slow down > > Chris > > -Original Message- > Sent: 04 September 2002 16:50 > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > > > On Wednesday 04 September 2002 04:23, [EMAIL PROTECTED] wrote: > > It sounds to me like the indexes are going into overflow - this will cause > > What do you mean by 'overflow'? > > Jared > > > the insert time to increase. I would suggest batching up the inserts, > > dropping the indexes, running the inserts and re-creating the indexes. > > > > Chris > > > > -Original Message- > > Sent: 04 September 2002 07:53 > > To: Multiple recipients of list ORACLE-L > > > > > > Hi All, > > > > We have a table which can contain more than half a million records. When > we > > try to insert some 10k records in the empty table it get inserted in 10 > > min. but as the size increases time taken to insert also increases. After > > 350,000 records it takes around an hour to insert 10k records. > > There are around 15 columns in it out of which 11 are indexed. There is > one > > concatenated function-based index on two columns of Varchar type and two > > separate index for the same two columns. > > > > I have checked the free space for the tablespaces to which the table and > > indexes are attached to. They are in two separate tbs. > > > > Any clues why this is happenning. > > > > TIA > > Marul. > > > Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1" > Content-Transfer-Encoding: 7bit > Content-Description: > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Marul Mehta INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Inserts are taking time !
No there is not a single bitmap indexes. We had previously but than removed all and converted to normal b-tree indexes. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, September 04, 2002 4:58 PM > Marul, > > Are there any bitmapped indexes on the table > > Iain Nicoll > > -Original Message- > Sent: Wednesday, September 04, 2002 11:28 AM > To: Multiple recipients of list ORACLE-L > > > Marul, > > 10k records in 1 hour(3600 seconds) > > 1 record in 3600/1 => approx 0.36 seconds > > If your application is OLTP you'll be inserting records 1 by 1 rather than > in > bulk. Which means the effect will hardly be noticed. > > If you are going to insert record in bulk you can DROP and then recreate the > indexes after load. > > Check what takes more time. > > See if there is any scope of partitioning the table, to use local > partitioned > indexes. > > For bulk load, disabling the constraints is also an option. > > Naveen > > -Original Message- > Sent: Wednesday, September 04, 2002 3:13 PM > To: Multiple recipients of list ORACLE-L > > > Thanks for the immediate reply > But my requirement is such that I cannot reduce the indexes. There are lots > of selects happeneing on this table based on these indexed columns. Our > entire application is about to move in the production environment and we > cant change our DB design at this time. > > Please suggest > > TIA, > Marul. > > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Wednesday, September 04, 2002 1:33 PM > > > > Yep and you have given the answer yourself. It is the number of indexes. I > > think that if the number of records increase the number of levels increase > > and slowly but surely you need to update more and more blocks. I have done > > sone tests (an oher people I am sure) that show that there is an expontial > > increase in the amount of undo and redo generated for every index that > gets > > added into the mix. > > > > You will probably see an increase in CPU time (assuming that you are the > only > > process/session on the system). > > > > Anjo. > > > > > > On Wednesday 04 September 2002 08:53, you wrote: > > > Hi All, > > > > > > We have a table which can contain more than half a million records. When > we > > > try to insert some 10k records in the empty table it get inserted in 10 > > > min. but as the size increases time taken to insert also increases. > After > > > 350,000 records it takes around an hour to insert 10k records. There are > > > around 15 columns in it out of which 11 are indexed. There is one > > > concatenated function-based index on two columns of Varchar type and two > > > separate index for the same two columns. > > > > > > I have checked the free space for the tablespaces to which the table and > > > indexes are attached to. They are in two separate tbs. > > > > > > Any clues why this is happenning. > > > > > > > > > TIA > > > Marul. > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Anjo Kolk > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California-- Public Internet access / Mailing Lists > > > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Marul Mehta > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HEL
Re: Inserts are taking time !
Thanks for the immediate reply But my requirement is such that I cannot reduce the indexes. There are lots of selects happeneing on this table based on these indexed columns. Our entire application is about to move in the production environment and we cant change our DB design at this time. Please suggest TIA, Marul. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, September 04, 2002 1:33 PM > Yep and you have given the answer yourself. It is the number of indexes. I > think that if the number of records increase the number of levels increase > and slowly but surely you need to update more and more blocks. I have done > sone tests (an oher people I am sure) that show that there is an expontial > increase in the amount of undo and redo generated for every index that gets > added into the mix. > > You will probably see an increase in CPU time (assuming that you are the only > process/session on the system). > > Anjo. > > > On Wednesday 04 September 2002 08:53, you wrote: > > Hi All, > > > > We have a table which can contain more than half a million records. When we > > try to insert some 10k records in the empty table it get inserted in 10 > > min. but as the size increases time taken to insert also increases. After > > 350,000 records it takes around an hour to insert 10k records. There are > > around 15 columns in it out of which 11 are indexed. There is one > > concatenated function-based index on two columns of Varchar type and two > > separate index for the same two columns. > > > > I have checked the free space for the tablespaces to which the table and > > indexes are attached to. They are in two separate tbs. > > > > Any clues why this is happenning. > > > > > > TIA > > Marul. > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Anjo Kolk > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Marul Mehta INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Inserts are taking time !
Hi All, We have a table which can contain more than half a million records. When we try to insert some 10k records in the empty table it get inserted in 10 min. but as the size increases time taken to insert also increases. After 350,000 records it takes around an hour to insert 10k records. There are around 15 columns in it out of which 11 are indexed. There is one concatenated function-based index on two columns of Varchar type and two separate index for the same two columns. I have checked the free space for the tablespaces to which the table and indexes are attached to. They are in two separate tbs. Any clues why this is happenning. TIA Marul.
Re: Disabling indexes - temporarily
Thanks a lot to all who have contributed their experiences and ideas for thsi problem. I have to look into the application and business details whether this is feasible or not. Have to talk to our tech head. Marul. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, September 02, 2002 6:48 PM > Iain, > > thats a fantastic idea. > > Naveen > > -Original Message- > Sent: Monday, September 02, 2002 5:28 PM > To: Multiple recipients of list ORACLE-L > > > Could you have a trigger which before insert, inserts into another empty > table with exactly same layout but rejects the insert on the main table. > Then disables the trigger and adds these at a non-busy stage and reenables > the trigger. Would be a whole lot quicker if it's possible. > > > > -Original Message- > Sent: Monday, September 02, 2002 11:38 AM > To: Multiple recipients of list ORACLE-L > > > Thanks Naveen, > Lets forget about the statistics and performance, but I have such type of > requirenment than is there any way out ? > > Marul. > > - Original Message - > To: Multiple recipients of list ORACLE-L <mailto:[EMAIL PROTECTED]> > Sent: Saturday, August 31, 2002 11:58 PM > > Firstly, you are only inserting 100-400 records daily, which is not a big > deal. Even if there was a way to stop the indexes from getting updated, it > won't increase the performance by a noticable amount. > > Secondly, there is no way(as far as i know) to make the indexes READ-ONLY > with the table in READ-WRITE mode. > > Thirdly, rebuilding 20 indexes on a table with 1 million record will take a > long time, in comparison updation by 100-400 records is nothing. > > It neither feasible nor advisable. > > Naveen > > -Original Message- > Sent: Saturday, August 31, 2002 11:08 PM > To: Multiple recipients of list ORACLE-L > > > Hi all, > > Need to know if the following is possible in Oracle(any version):- > > I have a table of around > (a) 30 Columns > (b) 20 out of 30 are indexed > (c) around 1 million (1,000,000) records. > > Most of the time there will be heavy reads (select queries) on this table > except for some 100-400 records to be inserted in a day. The newly inserted > records will not be selected by the queries for the next 24 hours (this is > based on some business logic), thats for sure. > > Now the problem is when ever a record(s) is inserted the entire bunch of > indexes is updated/rebuild by the Oracle which considerably slows down the > throughput of the system during that period of time (until all indexes are > updated). > > Can we have a solution whereby indexes should not be updated when a > record(s) is inserted, because I know that these records will not be the > part of the query for the next 24 hrs. The indexes will be re-built > manually/scheduled during the off-peak hours once a day. In this way, the > next day, new records inserted a day before will be ready to be fetched by > the queries. > > Note- I can't put my indexes offline not for a single minute during peak > hours. > > Any clues? > > TIA, > Marul. > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Nicoll, Iain \(Calanais\) > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Naveen Nahata > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Marul Mehta INET:
Re: Disabling indexes - temporarily
Thanks Naveen, Lets forget about the statistics and performance, but I have such type of requirenment than is there any way out ? Marul. - Original Message - From: Naveen Nahata To: Multiple recipients of list ORACLE-L Sent: Saturday, August 31, 2002 11:58 PM Subject: RE: Disabling indexes - temporarily Firstly, you are only inserting 100-400 records daily, which is not a big deal. Even if there was a way to stop the indexes from getting updated, it won't increase the performance by a noticable amount. Secondly, there is no way(as far as i know) to make the indexes READ-ONLY with the table in READ-WRITE mode. Thirdly, rebuilding 20 indexes on a table with 1 million record will take a long time, in comparison updation by 100-400 records is nothing. It neither feasible nor advisable. Naveen -Original Message-From: Marul Mehta [mailto:[EMAIL PROTECTED]]Sent: Saturday, August 31, 2002 11:08 PMTo: Multiple recipients of list ORACLE-LSubject: Disabling indexes - temporarily Hi all, Need to know if the following is possible in Oracle(any version):- I have a table of around (a) 30 Columns (b) 20 out of 30 are indexed (c) around 1 million (1,000,000) records. Most of the time there will be heavy reads (select queries) on this table except for some 100-400 records to be inserted in a day. The newly inserted records will not be selected by the queries for the next 24 hours (this is based on some business logic), thats for sure. Now the problem is when ever a record(s) is inserted the entire bunch of indexes is updated/rebuild by the Oracle which considerably slows down the throughput of the system during that period of time (until all indexes are updated). Can we have a solution whereby indexes should not be updated when a record(s) is inserted, because I know that these records will not be the part of the query for the next 24 hrs. The indexes will be re-built manually/scheduled during the off-peak hours once a day. In this way, the next day, new records inserted a day before will be ready to be fetched by the queries. Note- I can't put my indexes offline not for a single minute during peak hours. Any clues? TIA, Marul.
Disabling indexes - temporarily
Hi all, Need to know if the following is possible in Oracle(any version):- I have a table of around (a) 30 Columns (b) 20 out of 30 are indexed (c) around 1 million (1,000,000) records. Most of the time there will be heavy reads (select queries) on this table except for some 100-400 records to be inserted in a day. The newly inserted records will not be selected by the queries for the next 24 hours (this is based on some business logic), thats for sure. Now the problem is when ever a record(s) is inserted the entire bunch of indexes is updated/rebuild by the Oracle which considerably slows down the throughput of the system during that period of time (until all indexes are updated). Can we have a solution whereby indexes should not be updated when a record(s) is inserted, because I know that these records will not be the part of the query for the next 24 hrs. The indexes will be re-built manually/scheduled during the off-peak hours once a day. In this way, the next day, new records inserted a day before will be ready to be fetched by the queries. Note- I can't put my indexes offline not for a single minute during peak hours. Any clues? TIA, Marul.
Re: Function-Based Index not working
Even after giving the hint its not working.
I guess you can't have IS clause and Like with
function-based index.
Marul.
- Original Message -
From:
Naveen Nahata
To: Multiple recipients of list ORACLE-L
Sent: Saturday, August 31, 2002 7:28
PM
Subject: RE: Function-Based Index not
working
I
think everythying is fine. Did you try index hint? try that and see.
if
that also doesn't work, then either we are missing something or the Optimizer
thinks so
Naveen
-Original Message-From: Marul Mehta
[mailto:[EMAIL PROTECTED]]Sent: Saturday, August 31, 2002 6:33
PMTo: Multiple recipients of list ORACLE-LSubject: Re:
Function-Based Index not working
Hi Naveen,
Thanks a lot for the efforts you are putting in
for me for such a simple problem, but unfortunately, for me all the tips and
tricks are not solving the problem.
Now these are my current
statistics :-
+ alter session set
QUERY_REWRITE_ENABLED=TRUE;+ alter session set
QUERY_REWRITE_INTEGRITY=TRUSTED;+ alter session set
optimizer_mode=FIRST_ROWS;+ alter session set
DB_FILE_MULTIBLOCK_READ_COUNT=1;
This procedure writes 180,000 records in
employeees table
+ execute bulk_insert
Analyzing table and rebuilding index (though
its not necessary)
+ analyze table employees compute
statistics;
+ alter index upper_ix
rebuild;
Making autotrace on
+ set autotrace traceonly explain
Fired the query:
SELECT last_name FROM employees WHERE
UPPER(last_name) IS NOT NULL ORDER BY UPPER(last_name);Elapsed:
00:00:00.00
Execution
Plan--
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=57
Card=4001 Bytes=2
0005)
1 0
SORT (ORDER BY) (Cost=57 Card=4001 Bytes=20005)
2 1 TABLE ACCESS (FULL) OF
'EMPLOYEES' (Cost=38 Card=4001
By
tes=20005)
Any clues what is happening? Should I insert
more records in the table.
TIA,
Marul.
- Original Message -
From:
Naveen Nahata
To: Multiple recipients of list
ORACLE-L
Sent: Saturday, August 31, 2002 4:58
PM
Subject: RE: Function-Based Index not
working
See the table's size is very small. Till it atleast 2 times the
value of DB_BLOCK_SIZE * DB_FILE_MULTIBLOCK_READ_COUNT it will not use
index.
Set the value of DB_FILE_MULTIBLOCK_READ_COUNT to
one.
Insert lots
of values in the table. You can make a procedure to insert random
characters into the table, and then put it in a big loop. Analyze table
and thn run the same query.
It should work
naveen
-Original
Message-From: Marul Mehta
[mailto:[EMAIL PROTECTED]]Sent: Saturday, August 31, 2002 4:03
PMTo: Multiple recipients of list ORACLE-LSubject:
Re: Function-Based Index not working
Thanks a lot Naveen,
Even after executing the following the
execution plan shows full table scan :-
+ alter session set
QUERY_REWRITE_ENABLED=TRUE;+ alter session set
QUERY_REWRITE_INTEGRITY=TRUSTED;+ alter session set
optimizer_mode=FIRST_ROWS;
+ Insert into employees
values('A');
+ Insert into employees
values('B');
+ analyze table employees compute statistics;
+
select last_name FROM employees
WHERE UPPER(last_name) IS NOT NULL ORDER BY
UPPER(last_name); 2 3Elapsed:
00:00:00.00
Execution
Plan--
0 SELECT STATEMENT Optimizer=FIRST_ROWS
(Cost=3 Card=2
Bytes=2
)
1 0 SORT (ORDER BY)
(Cost=3 Card=2 Bytes=2) 2
1 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=1
Card=2 Bytes=
2)
Even after using the hint no change in the plan :-
+ select /* INDEX employees(upper_ix) */ last_name FROM
employees WHERE UPPER(last_name) IS NOT NULL;
Please tell me what else should I do to make this query use the
index which is created.
TIA,
Marul.
- Original Message -
From:
Naveen Nahata
To: Multiple recipients of list
ORACLE-L
Sent: Saturday, August 31, 2002
3:03 PM
Subject: RE: Function-Based Index
not working
Re: Function-Based Index not working
Hi All,
Thanks a lot to you all. At last I got the
function-based index working properly.
This is what I noticed :-
Have to alter session/system for :-
+ alter session set
QUERY_REWRITE_ENABLED=TRUE;+ alter session set
QUERY_REWRITE_INTEGRITY=TRUSTED;+ alter session set
optimizer_mode=FIRST_ROWS;
And
+ can't use IS NULL & IS NOT NULL
clause.
+ can't use Like operator.
Regards,
Marul.
- Original Message -
From:
Marul Mehta
To: Multiple recipients of list ORACLE-L
Sent: Saturday, August 31, 2002 6:33
PM
Subject: Re: Function-Based Index not
working
Hi Naveen,
Thanks a lot for the efforts you are putting in
for me for such a simple problem, but unfortunately, for me all the tips and
tricks are not solving the problem.
Now these are my current
statistics :-
+ alter session set
QUERY_REWRITE_ENABLED=TRUE;+ alter session set
QUERY_REWRITE_INTEGRITY=TRUSTED;+ alter session set
optimizer_mode=FIRST_ROWS;+ alter session set
DB_FILE_MULTIBLOCK_READ_COUNT=1;
This procedure writes 180,000 records in
employeees table
+ execute bulk_insert
Analyzing table and rebuilding index (though its
not necessary)
+ analyze table employees compute
statistics;
+ alter index upper_ix rebuild;
Making autotrace on
+ set autotrace traceonly explain
Fired the query:
SELECT last_name FROM employees WHERE
UPPER(last_name) IS NOT NULL ORDER BY UPPER(last_name);Elapsed:
00:00:00.00
Execution
Plan--
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=57
Card=4001 Bytes=2
0005)
1 0
SORT (ORDER BY) (Cost=57 Card=4001 Bytes=20005)
2 1 TABLE ACCESS (FULL) OF
'EMPLOYEES' (Cost=38 Card=4001
By
tes=20005)
Any clues what is happening? Should I insert more
records in the table.
TIA,
Marul.
- Original Message -
From:
Naveen Nahata
To: Multiple recipients of list ORACLE-L
Sent: Saturday, August 31, 2002 4:58
PM
Subject: RE: Function-Based Index not
working
See the table's size is very small. Till it atleast 2 times the value
of DB_BLOCK_SIZE * DB_FILE_MULTIBLOCK_READ_COUNT it will not use
index.
Set the value of DB_FILE_MULTIBLOCK_READ_COUNT to
one.
Insert lots
of values in the table. You can make a procedure to insert random characters
into the table, and then put it in a big loop. Analyze table and thn run the
same query.
It should work
naveen
-Original
Message-From: Marul Mehta
[mailto:[EMAIL PROTECTED]]Sent: Saturday, August 31, 2002 4:03
PMTo: Multiple recipients of list ORACLE-LSubject: Re:
Function-Based Index not working
Thanks a lot Naveen,
Even after executing the following the
execution plan shows full table scan :-
+ alter session set
QUERY_REWRITE_ENABLED=TRUE;+ alter session set
QUERY_REWRITE_INTEGRITY=TRUSTED;+ alter session set
optimizer_mode=FIRST_ROWS;
+ Insert into employees
values('A');
+ Insert into employees
values('B');
+ analyze table employees compute statistics;
+
select last_name FROM employees
WHERE UPPER(last_name) IS NOT NULL ORDER BY
UPPER(last_name); 2 3Elapsed:
00:00:00.00
Execution
Plan--
0 SELECT STATEMENT Optimizer=FIRST_ROWS
(Cost=3 Card=2
Bytes=2 )
1 0 SORT (ORDER BY)
(Cost=3 Card=2 Bytes=2) 2
1 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=1
Card=2 Bytes=
2)
Even after using the hint no change in the plan :-
+ select /* INDEX employees(upper_ix) */ last_name FROM
employees WHERE UPPER(last_name) IS NOT NULL;
Please tell me what else should I do to make this query use the index
which is created.
TIA,
Marul.
- Original Message -
From:
Naveen Nahata
To: Multiple recipients of list
ORACLE-L
Sent: Saturday, August 31, 2002
3:03 PM
Subject: RE: Function-Based Index
not working
Marul,
1. you don't have table analyzed in which case Rule based
optimizer will be used. CBO is used if atleast one of the tables in the
query is ANALYZED
2. There is no data in your table. Optimizer goes for a full
tablescan if it thinks that it will be moer advisable to do a full table
scan. e.g. You will not use the INDEX if your book has only one
Re: Function-Based Index not working
Hi Naveen,
Thanks a lot for the efforts you are putting in for
me for such a simple problem, but unfortunately, for me all the tips and tricks
are not solving the problem.
Now these are my current
statistics :-
+ alter session set
QUERY_REWRITE_ENABLED=TRUE;+ alter session set
QUERY_REWRITE_INTEGRITY=TRUSTED;+ alter session set
optimizer_mode=FIRST_ROWS;+ alter session set
DB_FILE_MULTIBLOCK_READ_COUNT=1;
This procedure writes 180,000 records in employeees
table
+ execute bulk_insert
Analyzing table and rebuilding index (though its
not necessary)
+ analyze table employees compute
statistics;
+ alter index upper_ix rebuild;
Making autotrace on
+ set autotrace traceonly explain
Fired the query:
SELECT last_name FROM employees WHERE
UPPER(last_name) IS NOT NULL ORDER BY UPPER(last_name);Elapsed:
00:00:00.00
Execution
Plan--
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=57
Card=4001 Bytes=2
0005)
1 0 SORT
(ORDER BY) (Cost=57 Card=4001 Bytes=20005) 2
1 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=38 Card=4001
By
tes=20005)
Any clues what is happening? Should I insert more
records in the table.
TIA,
Marul.
- Original Message -
From:
Naveen Nahata
To: Multiple recipients of list ORACLE-L
Sent: Saturday, August 31, 2002 4:58
PM
Subject: RE: Function-Based Index not
working
See
the table's size is very small. Till it atleast 2 times the value of
DB_BLOCK_SIZE * DB_FILE_MULTIBLOCK_READ_COUNT it will not use
index.
Set
the value of DB_FILE_MULTIBLOCK_READ_COUNT to one.
Insert lots of values in
the table. You can make a procedure to insert random characters into the
table, and then put it in a big loop. Analyze table and thn run the same
query.
It should work
naveen
-Original
Message-----From: Marul Mehta
[mailto:[EMAIL PROTECTED]]Sent: Saturday, August 31, 2002 4:03
PMTo: Multiple recipients of list ORACLE-LSubject: Re:
Function-Based Index not working
Thanks a lot Naveen,
Even after executing the following the
execution plan shows full table scan :-
+ alter session set
QUERY_REWRITE_ENABLED=TRUE;+ alter session set
QUERY_REWRITE_INTEGRITY=TRUSTED;+ alter session set
optimizer_mode=FIRST_ROWS;
+ Insert into employees
values('A');
+ Insert into employees
values('B');
+ analyze table employees compute statistics;
+
select last_name FROM employees
WHERE UPPER(last_name) IS NOT NULL ORDER BY
UPPER(last_name); 2 3Elapsed: 00:00:00.00
Execution
Plan--
0 SELECT STATEMENT Optimizer=FIRST_ROWS
(Cost=3 Card=2
Bytes=2 )
1 0 SORT (ORDER BY) (Cost=3
Card=2 Bytes=2) 2
1 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=1 Card=2
Bytes= 2)
Even after using the hint no change in the plan :-
+ select /* INDEX employees(upper_ix) */ last_name FROM employees
WHERE UPPER(last_name) IS NOT NULL;
Please tell me what else should I do to make this query use the index
which is created.
TIA,
Marul.
- Original Message -
From:
Naveen Nahata
To: Multiple recipients of list
ORACLE-L
Sent: Saturday, August 31, 2002 3:03
PM
Subject: RE: Function-Based Index not
working
Marul,
1. you don't have table analyzed in which case Rule based optimizer
will be used. CBO is used if atleast one of the tables in the query is
ANALYZED
2. There is no data in your table. Optimizer goes for a full
tablescan if it thinks that it will be moer advisable to do a full table
scan. e.g. You will not use the INDEX if your book has only one
page.
The decision of going for a full tablescan is based on
DB_BLOCK_SIZE * DB_FILE_MULTI_BLOCK_READCOUNT, which tells how much data
Oracle fetches at one time. If your entire table can be fetched in atleast
2 fetches, full table scan will be done instead of INDEX scan, to avoid
doubling of work.
Naveen
-Original Message-From: Marul Mehta
[mailto:[EMAIL PROTECTED]]Sent: Saturday, August 31, 2002 2:18
PMTo: Multiple recipients of list ORACLE-LSubject:
Function-Based Index not working
Hi,
Can you please help me out in solving this
weird problem of funcation-based index not being used when I query the
table.
This is the comand I fired and the result
it returned me.
1. SQL> create table employees
Re: Function-Based Index not working
Thanks a lot Naveen,
Even after executing the following the execution
plan shows full table scan :-
+ alter session set
QUERY_REWRITE_ENABLED=TRUE;+ alter session set
QUERY_REWRITE_INTEGRITY=TRUSTED;+ alter session set
optimizer_mode=FIRST_ROWS;
+ Insert into employees values('A');
+ Insert into employees values('B');
+ analyze table employees compute statistics;
+
select last_name FROM employees WHERE
UPPER(last_name) IS NOT NULL ORDER BY UPPER(last_name);
2 3Elapsed: 00:00:00.00
Execution
Plan--
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=3
Card=2 Bytes=2 )
1 0 SORT (ORDER BY) (Cost=3
Card=2 Bytes=2) 2 1
TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=1 Card=2
Bytes= 2)
Even after using the hint no change in the plan :-
+ select /* INDEX employees(upper_ix) */ last_name FROM employees
WHERE UPPER(last_name) IS NOT NULL;
Please tell me what else should I do to make this query use the index which
is created.
TIA,
Marul.
- Original Message -
From:
Naveen Nahata
To: Multiple recipients of list ORACLE-L
Sent: Saturday, August 31, 2002 3:03
PM
Subject: RE: Function-Based Index not
working
Marul,
1.
you don't have table analyzed in which case Rule based optimizer will be used.
CBO is used if atleast one of the tables in the query is
ANALYZED
2.
There is no data in your table. Optimizer goes for a full tablescan if it
thinks that it will be moer advisable to do a full table scan. e.g. You will
not use the INDEX if your book has only one page.
The
decision of going for a full tablescan is based on DB_BLOCK_SIZE *
DB_FILE_MULTI_BLOCK_READCOUNT, which tells how much data Oracle fetches at one
time. If your entire table can be fetched in atleast 2 fetches, full table
scan will be done instead of INDEX scan, to avoid doubling of
work.
Naveen
-----Original Message-From: Marul Mehta
[mailto:[EMAIL PROTECTED]]Sent: Saturday, August 31, 2002 2:18
PMTo: Multiple recipients of list ORACLE-LSubject:
Function-Based Index not working
Hi,
Can you please help me out in solving this
weird problem of funcation-based index not being used when I query the
table.
This is the comand I fired and the result it
returned me.
1. SQL> create table employees
(last_name varchar2(20));
Table created.
2. SQL> CREATE INDEX upper_ix ON employees
(UPPER(last_name));
Index created.
Made the autotrace on and than:-
3. SELECT last_name FROM employees WHERE
UPPER(last_name) IS NOT NULL ORDER BY UPPER(last_name);
no rows
selected.
Execution
Plan--
0 SELECT STATEMENT
Optimizer=CHOOSE 1 0 SORT
(ORDER BY) 2 1
TABLE ACCESS (FULL) OF 'EMPLOYEES'
I fired without order by clause also but no
use.
Now can any body please let tell me why this
Oracle is having a full scan of the employee table.
TIA,
Marul.
Function-Based Index not working
Hi, Can you please help me out in solving this weird problem of funcation-based index not being used when I query the table. This is the comand I fired and the result it returned me. 1. SQL> create table employees (last_name varchar2(20)); Table created. 2. SQL> CREATE INDEX upper_ix ON employees (UPPER(last_name)); Index created. Made the autotrace on and than:- 3. SELECT last_name FROM employees WHERE UPPER(last_name) IS NOT NULL ORDER BY UPPER(last_name); no rows selected. Execution Plan-- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (ORDER BY) 2 1 TABLE ACCESS (FULL) OF 'EMPLOYEES' I fired without order by clause also but no use. Now can any body please let tell me why this Oracle is having a full scan of the employee table. TIA, Marul.
Re: double quotes column name
I guess this is not the case.
1. create table t("TABLE" Varchar2(10));
2. select table_name,column_name from user_tab_columns where table_name='T';
TABLE_NAME COLUMN_NAME
-- --
T TABLE
Even though TABLE is a reserved word it appeared in uppercase when
user_tab_columns was queried.
Marul.
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, August 21, 2002 11:38 PM
> > how can we know that this table is created with "" column name
>
> When the column name is a reserved word, or appears with lower
> case characters.
>
> Jared
>
>
>
>
>
>
> "Harvinder Singh" <[EMAIL PROTECTED]>
> Sent by: [EMAIL PROTECTED]
> 08/21/2002 09:28 AM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
> cc:
> Subject:double quotes column name
>
>
> Hi,
>
> One of our clients has created a table with column name "access"
> since access is reserved word , they use double quotes.
> but when i query the table user_tab_columns it is still showing column
> name as access and not "access"
> how can we know that this table is created with "" column name..
>
> Thanks
> --Harvinder
>
>
> SQL> select table_name,column_name from user_tab_columns
> 2 where table_name='FF1'
> 3 /
>
> TABLE_NAME COLUMN_NAME
> -- --
> FF1access
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Harvinder Singh
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Marul Mehta
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Encryption of data
Title: RE: Encryption of data What about encrypting the entire tablespace? has anybody tried this before? Thanks, Marul - Original Message - From: Amar Kumar Padhi To: Multiple recipients of list ORACLE-L Sent: Sunday, August 11, 2002 11:04 AM Subject: RE: Encryption of data Try dbms_obfuscation_toolkit.It supports DES, DES3 and MD5 encryption formats. You can use these for storing encrypted information in tables. rgds amar http://amzone.netfirms.com -Original Message- From: Manavendra Gupta [mailto:[EMAIL PROTECTED]] Sent: Saturday, August 10, 2002 8:34 PM To: Multiple recipients of list ORACLE-L Subject: Encryption of data What is the best way to encrypt data in Oracle (like username/passwords, etc)? Is there a provision to encrypt the entire table/tablespace ? Thanks, Manav. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Manavendra Gupta INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Unix Solaris forum.
I looked for www.sunmangers.org but it doesnt work? Can you please re-check the reference. Thanks. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Sunday, August 11, 2002 9:18 PM > www.sunmangers.org will do it for you > > Cheers > > > -- > = > Peter McLarty E-mail: [EMAIL PROTECTED] > Technical ConsultantWWW: http://www.mincom.com > APAC Technical Services Phone: +61 (0)7 3303 3461 > Brisbane, AustraliaMobile: +61 (0)402 094 238 > Facsimile: +61 (0)7 3303 3048 > = > A great pleasure in life is doing what people say you cannot do. > > - Walter Bagehot (1826-1877 British Economist) > = > Mincom "The People, The Experience, The Vision" > > = > > This transmission is for the intended addressee only and is confidential > information. If you have received this transmission in error, please > delete it and notify the sender. The contents of this e-mail are the > opinion of the writer only and are not endorsed by the Mincom Group of > companies unless expressly stated otherwise. > > > > > > > "Chuan Zhang" <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 12-08-2002 01:03 PM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > cc: > Fax to: > Subject:Unix solaris forum. > > > > > Hi, DBAs, > > Could anyone recommend a good unix solaris discussion/news group for me? > > > Thanks, > > Chuan > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Marul Mehta INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Optimized solution for large amount of data
Hi all, I have a table with two columns of type:- (a) Number - Primary Key (b) BLOB The size of binary data in blob column can range from 200-400 KB and there will be around 40,000 records over the period of one year. This tantamount to around 12GB of data (300KB(avg.) * 40,000). Read and writes will not be very frequent; around 2-3 inserts/deletes and 10-15 read per day. There are around 30 tables more where data access is very heavy. There are no restrictions on hardware that can be plugged-in. Can any please tell me the optimized solution for storing this type of table(s)? What should be the database parameters and hardware configuration? We will be using Oracle 8.1.6 on Redhat Linux 6.2 or Suse Linux 7.0. Thanks a lot, Marul. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Marul Mehta INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
