SQL & PL/SQL :: Indexes With Wild Card At Both Sides?
Sep 2, 2010optimising the below query ....
SELECT
CBU_CD,
MKTG_SUB_SEGMENT_A_NM,
BILLG_SYSTEM_ID,
BILLG_ACCT_NO,
[code]...
optimising the below query ....
SELECT
CBU_CD,
MKTG_SUB_SEGMENT_A_NM,
BILLG_SYSTEM_ID,
BILLG_ACCT_NO,
[code]...
i want to create two databases with different sids on one oracle home.
how can ı handle it?
I wonder if you have any examples (codes) to achieve communication from forms 6i with a card reader.
View 1 Replies View RelatedOur product needs to use SecureMag Encrypted MagStripe Reader to get the credit card info in POS.After adding the code in every module, most of them work well now. But one of them does not. We met the error FRM-41344.This module is called from another(using call_form) that could also have this functionality(it works well in this module).
the code in WHEN-NEW-FORM-INSTANCE trigger:
go_item('CONTROL.CODE');
:global.msrDevice := :ITEM('CONTROL.opos').interface;
The error raise in this code. The global variable is assigned a value in each module.I got some info from the internet. But I still get error after I did the following operation.When using Oracle Forms, you might receive this run-time error:
FRM-41344: OLE object not defined for object in current record.
which can occur for either of these reasons: The OLE container has lost the definition of the Oracle Video Custom Control. To fix this problem, go into the Forms Designer, and re-insert the Oracle Video Custom Control by clicking the right mouse button inside the OLE container and choosing Insert Object
The Oracle Video Custom Control has not been initialized. To fix this problem, modify the form so that it can navigate to the block that contains the Oracle Video Custom Control. You can either make this block the first block on the form or add a GO_BLOCK command in the WHEN-NEW-FORM-INSTANCE script to navigate to that block. If necessary, you can add a GO_BLOCK command followed by SYNCHRONIZE before any commands that access the Oracle Video Control. (You can tell if the Oracle Video Control has been initialized because the video control buttons will be visible.)
RDBMS - 11.1.0.7, I it possible to convert indexes of a non-partitioned table to hash partitioned indexes by retaining table as non-partitioned?
If yes, is this what it is Creating a Hash-Partitioned Global Index - can be created for partitioned and non-partitioned tables?
this is just a generic question. If a table has multiple Indexes on it say a table of 20 columns where 8 columns have indexes on it (each columns out of the 8 has an index on it and out of which three columns contain unique indexes on it).
If i run a query on this table where only two columns are specified in the where clause are the remaining 6 indexes on the table scanned during query execution.
on 11g R2 on Win 2008
How to find ddl for any indexes built on MY_VW ?
If I have owner, table_name is there a query I can issue that will tell me if I have to add the "update global indexes" clause when dropping a partition from a table?
View 1 Replies View RelatedWhy we can create indexes only on materialized view and not on normal views?
View 8 Replies View RelatedSay we have an employee(id_emp) table with a primary key on id_emp. We have also some history tables emp_stuff with columns say (id_emp, dat_event, some_stuff) with primary key id_emp, dat_event.
This means that we have a unique index on (id_emp,dat_event). We also have a foreign key id_emp that references employee(id_emp). When we update id_emp on employee, we still have a lock on emp_stuff. According to this (end of the page) :
Quote:So, in short, with releases prior to Oracle Database 11g Release 1, you will want an index on the foreign key of the child table if you do any of the following:
Update the parent table primary key
Delete from the parent table
Merge into the parent table
So is id_emp in emp_stuff considered as indexed (through the unique index of the primary key) or do we have to add an explicit index
like this CREATE INDEX emp_stuff ON emp_stuff(id_emp) to avoide child table locks?
I have noticed a very questionable phrase on an article updated in 2011: "Oracle SE may allow you to create a function-based index, but you must pay for an EE license to use FBI's." [URL] Is this true? I have tested a FBI on my SE and works just fine.
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
SQL> create table t ( col varchar2(10));
Table created.
SQL> create index t_idx on t(upper(col));
Index created.
SQL> insert into t values('a');
[code]....
Lot of my tables are just for join purpose , a table with just with two columns both FK , I did not add any composite PK to this table, lot my queries uses this table , because it does not have composite PK will the query be slow ? what I have to do to increase performance querying with such tables ?
View 5 Replies View RelatedHow many different types of indexes we have in oracle?
View 2 Replies View RelatedUsing Oracle 11.2.0.3
We are evaluating partition strategies with view to achieving performance gains in reporting in particular. How efficient are partitioned indexes in this regard e.g.
just partitioned indexes on an un partitioned table.
One large fact table with durrogate keys on which have bitmpa indexes which link to unique key in associated dimensions. Considering partitioning the bitmap index which links to the largest dimension and similarly partition the dimension key on largest dimension.
currently i am using below version
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
{CODE}
here is my query to find out the list of indexes and positions in a single schema.SELECT
user_indexes.TABLE_NAME||CHR(9)||
user_indexes.INDEX_NAME||CHR(9)||
TRIM(user_ind_columns.COLUMN_NAME)||CHR(9)||
user_ind_columns.column_position ||CHR(9)||
[code]...
i am trying to spool the output to .txt file. in textfile the output should be in below format :
Table_name Index_name column_names
---------------- ---------------- ------------------
ACS_TEST ACS_TEST_IDX COL1,COL2
C_COLLEGE INX_COLL STUDENT_ID,FIRST_NAME,LAST_NAME,DEPT_ID
Note : Based on the position value the column name have to be placed.
I am trying to find out database name for the unusable indexes thru the query; I am using dba_indexes for the index name but not sure which view I need to join with to find the db name.
View 11 Replies View RelatedHow can i check that indexes need rebuild?
View 6 Replies View RelatedDoes size (number of rows) of table and corresponding indexes have influence on INSERT operations (bigger table - slower insert)?
I supposed that if select returns the same rows in small and big table, there will be no difference in performance?
A basic select and group by query I am optimising for my Database course has returned results that indicate it will perform better on a clustered index when returning a smaller number of rows (5% of the largest table) and on a hash clustered index when returning higher volumes (50% and 80%). I understand that it is possible to use more than one index type on a table to improve performance, but I am struggling to understand how I might establish a hash cluster and a cluster on the same table? and then use hints to drive the query down one access path or the other.
Site admin - this site is a very useful resource.
I want drop some old partitions from big table but this will not increase free space on disk. So I want to move table with indexes to anothers tablespaces. What is the fastest way to do that? ALTER TABLE ... MOVE TABLESPACE ...? CTAS ? Or something else?
View 4 Replies View RelatedI have a few dimension tables with surrogate keys and a fact table with foreign keys on columns referring to the dimension table. I want to create primary keys on the dimension tables and foreign keys on the fact table, but with minimal overhead of constraint validation when loading the data.
Other than bitmap indexes on the FK columns on the fact table, I also want to create a bitmap join index on the fact table joined to the dimension table on a handful of dimension columns.
Which is the best suited constraint definition? Only RELY, or RELY + DISABLE, or RELY + DISABLE + NOVALIDATE, or RELY + NOVALIDATE, or any other?
I have created materiaized view
Create Materialized View table_name
Tablespace FG_Report
Build Immediate Refresh Force
Start with SYSDATE next SYSDATE+5/1440
As Select * from table_name@dblink;
after creating, i don't see indexes on the the materialized view created. where as in actual table we have indexes..
Can we replicate indexes along with Materialized views?
I have a task to create a procedure that disables (particular) partitioned indexes and REBUILD after ETL load.
Developers wants 3 parameters for the procedure:
1) TABLE_OWNER2) TABLE_NAME3) TRANSACTION_DATE = 13-JUN-12
And here is what I have done. I have created a procedure that takes 3 input parameters, please see the attached script:
1) TABLE_OWNER 2) TABLE_NAME 3) PARTITION_NAME - requires to query the particular partition to get the partition name
a situation where they will input dates as the partition was on a DATE column, now my challenge is how to incorporate this into the procedure to accept DATE as an input which will require one to query the particular table to get dates.I thought of using (HIGH_VALUE - 1) to get the dates from ALL_IND_PARTITIONS.
I know that truncate is a ddl operation that removes all the data from the table and set the HWM to very beginning. I am curious to know, is anyhow indexes will be affected on the issuance of truncate command or we need to rebuild the indexes after issuing truncate.
Also is there any way to know that how many rows/blocks a select statement is scanning because of the effect of HWM even the table has 0 rows.
Are NULLs get indexed in bitmap indexes? How can I verify that.
View 3 Replies View RelatedSo I was reading about indexes here:[URL]...1Is there any reason to NOT use an index? If there aren't, then should you use an index on every column on every table?
What is the general best practice with indexes? After reading the section, it seems that there are only positive impacts of using an index, so why are they not automatically created?
How to find indexes which are eligible to rebuild in the database environment? Is there any query to find it?
View 7 Replies View Relatedi am using oracle 10g. is there a way to find out the over fragmented tables/indexes ,so that reorganizing or rebuilding them will improve sql performajnce?
View 4 Replies View RelatedRDMS Version : 11.2.0.2
Platfomrm : AIX 6.1
For partitioned tables (RANGE, LIST types ) what are circumstances when a Global or a Local Index become UNUSABLE ? I was told that in some circumstances where the Indexes become UNUSABLE , not even a SELECT query against the table will work. Is this true ? For partitioned tables with Global Index, the global index will be listed in DBA_INDEXES . Right ?
I have a table like MyTab(a int, b int), and I am required to create a primary key index and a non-unique index on this table using columns (a,b) in a specific table space.
The back end database is Oracle 10g.
Here's what I have tried so far, needless to say, unsuccessfully.
Alter Table MyTab
Add Constraint c_1 primary key (a, b)
Using Index (Create index mytab_idx on MyTab(a, b))
Using index tablespace results_index
So my question are:
1. is this is possible? if so, what is the correct syntax.
2. assuming it is possible, using this sort of construct before? it appears to be conflicting and inconsistent to me.