473,603 Members | 2,638 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

simple question about indexes

Hello !

I create the index
CREATE INDEX details_loaded ON details (loaded);
where loaded - CHAR(1) DEFALUT 'N'.

The column details.loaded used to determine is this row
processed or not, so index details_loaded used to speed up
request when my program fetch not loaded records.

In a first release I use
UPDATE details SET loaded='Y' WHERE ...

but I hear later that Oracle do not index really rows with
NULL keys, so I birn my second variant:
UPDATE details SET loaded=NULL WHERE ...

as far as details table is huge and volatile, by this method
I tries minimize system overhead by this index - number of rows
WHERE loaded='N' is much less total number of rows.

Please let me know really this technique save my space and speed up
my request??
P.S. The RDBMS PostgreSQL have `conditional indexes` -
this way I can write

CREATE INDEX details_loaded ON details (loaded) WHERE loaded='N'
and DBMS will index only pointed rows (loaded='N'), but we have Oracle..
--
best regards,
Ruslan A Dautkhanov ru*****@scn.ru
Jul 19 '05 #1
0 1703

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1895
by: Paul | last post by:
I'm confused about something I did this week. I have an 8.1.7.4 database with tables partitioned by dates. I recently dropped my maxvalue tablespace, added a few more partitions for up-coming dates, then put the maxval partition back on. (I save the data that was in the maxval partition first and put it back in when I was done. These tables also contains BLOBS. All of my indexes were at a status of either "Valid" or "Usable" in the case of...
9
1882
by: pheonix1t | last post by:
hello, I've been assigned to do performance tuning on an SQL2000 database (around 10GB in size, several instances). So far, I see a single RAID5 array, 4CPU (xeon 700MHZ), 4GB RAM. I see the raid5 as a bottleneck. I'd setup a raid 10 and seperate the logs, database and OS(win2k). The one thing that was a bit odd to me was that I was told this place doesn't use indexes. The company is a house builder. They are pretty
5
6830
by: Yaro | last post by:
Hello I have odd problem with simple procedure (DB2. 8.1 FP10 Win32) CREATE PROCEDURE ADM.P_REORG_RUNSTATS(IN par_TabName VARCHAR(128), IN par_Reorg INTEGER,IN par_Runstats INTEGER) SPECIFIC P_REORG_RUNSTATS NOT DETERMINISTIC MODIFIES SQL DATA LANGUAGE SQL NO EXTERNAL ACTION
27
1832
by: karan.shashi | last post by:
Hey all, I was asked this question in an interview recently: Suppose you have the method signature bool MyPairSum(int array, int sum) the array has all unique values (no repeats), your task is to find two
14
19661
by: Jeff | last post by:
This is the first time that I remember ever having too many indexes on a table, but it has happened. I have en employees table and store in many places, on tables, the id of the employee that performed some action. Yes, I know, that could be in an audit trail but it isn't. For example, who printed a sales order, who processed it etc is stored on the sales orders table. Well, I have run out of indexes on the employees table when trying...
30
3499
by: galiorenye | last post by:
Hi, Given this code: A** ppA = new A*; A *pA = NULL; for(int i = 0; i < 10; ++i) { pA = ppA; //do something with pA
1
2148
by: Rahul Babbar | last post by:
Hi, I ran the scripts in a file from Command Line Processor and it gave the error for all the constraints being added, but not the indexes being added. For a simple statement like Alter table A add constraint A_const foreign key(col1) references B(col2);
8
2107
by: alexhguerra | last post by:
Hello If im not missing something, when looking into directories that are SMS containers i can see multiple files, one for table data, one for indexes and other for lobs. Is there any special reason for not having different files/containers/ tablespace for each index? if there's a recommendation by IBM to have one table per tablespace, why not having one file/tablespace for each index?
0
196
by: Ruslan A Dautkhanov | last post by:
Hello ! I create the index CREATE INDEX details_loaded ON details (loaded); where loaded - CHAR(1) DEFALUT 'N'. The column details.loaded used to determine is this row processed or not, so index details_loaded used to speed up request when my program fetch not loaded records.
0
7996
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
1
8060
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8273
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
6735
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
5878
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5441
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
3903
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
3951
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
0
1259
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.