gives different result when we exchange the index ksr_valid_until_i on table kreditkarten_sets_rs. For some reasons we changed the index from bitmap to normal and are getting different results. Switching back gives us the same results as before. When we avoid the usage of this index in the statement than we are getting the same results as when we are using the normal index.
I have to display a 24 hours time as an index. (00:00 - 23:00) That is easy, if the data exists.
But, if the data is not regularly exists on specific hour (the time/hours is not regular, i.e: 00:00, 01:00, 05:00, 08:00, 10:00, 11:00, 23:00), then it is OK for a TABLE, but not for a CHART.
Here is the story:
I wrote an SQL statement that can "choose" what to display respectively to the the available time. The result will be displayed on the chart: Days or Time is used for X-Axis, and the respective Value on the Y-Axis.
Example:
+ if the data consists of "days" (i.e: the last 5 days from now, 16/07/2012, 17/07/2012, 18/07/2012, 19/07/2012, 20/07/2012), then the chart will show those dates on the X-Axis.
+ if the data consists of "hours" (i.e: 1 single day, from 00:00 hour - 23:00), then the chart shows hours on the X-Axis.
If the 'hours' are not regular, means: there is no data on specific hours, the query result is also not regular, means: the X-Axis-value 'jumps' irregularly.
Note: the X-Axis on the chart template can be only customized for 1 single mode, example: "Days", but of course it is not going to show up properly if the query result are in "Hours". It means: I have to find the workaround on the SQL Query.
I was running a test on a huge table with a bitmap index on one of the columns. But bitmap index is not getting used. Below are the test details.
create table test (col1 number, col2 number);
begin for i in 1..1000000 loop if mod(i,2) = 0 then insert into test values(i, 'Y'); else insert into test values(i, 'N'); end if; end loop; end; COMMIT;
The intention here is to have only two distinct values in the entire table. Based on these values I will not build BITMAP index on col2.
CREATE BITMAP INDEX BITMAP_TEST on TEST(col2) COMPUTE STATISTICS;
Now when I run the below query, it doesn't uses BITMAP index. Instead the explain plan shows a full table scan.
select * from test where col2 = 'Y';
Now when I force ORACLE to use index through hint, the cost is too high while using the bitmap index(probably why the ORACLE chose not to use the index at the first stage).
I have read somewhere, that BITMAP index is useful when we have more than 1 or 2 bitmap indexes on other columns of the same table as well And the query should be like
select * from table where col1 = 'Y' and sex = 'F';
In this case oracle will use BITMAP but not in the case where there is only one column that has BITMAP index.
Considering all the factors stated above, is there any way I can fine tune my original query?
I was executing following query and it was taking about 20 sec's to execute before i decided to create B-Tree(Normal) index on column DeliVery.
Select DeliVery,Code,Sum(Units),Sum(Loads),Count(units), Count(Loads) Where
[code]...
After creating B-Tree index on Column Delivery the execution time has been reduced to one second ,thats what i wanted. But If i create Bitmap index on the same column then execution time is not getting reduced and is still same around 20 sec.I think theoratically "Delivery" column is the right candidate for Bitmap index? whether should i create bitmap index or stick with B-Tree index?
i want to create a bitmap index,but getting the error shown below..i created primary key on column dname of dept and unique constraint on empno of emp.
SQL> create bitmap index bindx on dept d(d.dname) from emp e,dept d where e.deptno=d.deptno; create bitmap index bindx on dept d(d.dname) from emp e,dept d where e.deptno=d.deptno * ERROR at line 1: ORA-25954: missing primary key or unique constraint on dimension
oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production PL/SQL Release 11.1.0.6.0 - Production "CORE 11.1.0.6.0 Production"
I have a procedure that is run weekly to load the data, for which it calls another procedure. This weekly procedure, disables the index, load the data, and rebuilds the index. During rebuilding of index, its giving ORA-00054: resource busy and acquire with NOWAIT error. I dont have privileges to look for session id, and kill the session. How to avoid this error.
create or replace PROCEDURE "WCL_WEEKLY_UPLOAD" ( p_event_id IN NUMBER ) [code]....
As you can see, after the procedure wc_upload, COMMIT is issued, and then the rebuilding of the index is starting. So dont know what is causing the table to lock.
is there some performance/access difference between a bitmap index on a number column and char(1) column? Both columns are not null with a default value.My application has a querie like this:
If I create a bitmap index on column "column_char", the access plan is not changed. But changing the column datatype to number(1) and obviously the values, the index is accessed and the cost decreases.This table has 4.000.000 rows. Oracle 11.2.0.2SO
I need to select a count of records where a field (call it widget) is the same, so i need all records where widgets are distinct. So it would be like asking for a distinct in a where clause. Not having much SQL experience this is a difficulty for me.
lets see .. so a count of records where widget = widget or something along those lines.
In the following merge statement in the USINg clause...I am using a select stament of one schema WEDB.But that same select statement should take data from 30 schemeas and then check the condition below condition
ON(source.DNO = target.DNO AND source.BNO=target.BNO);
I thought that using UNIONALL for select statement of the schemas as below.
I have a SQL statement that returns a set of columns...but...when I create table as <SQL statement> I get the same columns but with 2 of the columns containing each others data, e.g:
The SQL Select is correct and the Create Table As <SQL Select> is wrong.
Here is my SQL:
Create table ALTERNATENUMBERS as SELECT ctry, id, MAX(DECODE(tp,'EN', RN)) EN, MAX(DECODE(tp,'RN', RN)) RN, MAX(DECODE(tp,'AN', RN)) AN
[code]....
Unfortunately I cannot give you any data (too much of it) and small scale testing works, it's only when I run it on the 11million records do I get some (not all), just some of the data being mixed up between columns.
Now, I've tried:
1. Using SQLPLus - no joy
2. Creating the Table and then inserting the data into a blank table - also no joy
3. Using a VIEW - no joy, listagg doesn't work in VIEW tables
I do understand that without data it's hard to replicate the issue but why this statement works as a SELECT but when written to a table has data anomolies?
We are running on Oracle 10g. The following script results in ORA-00928: missing SELECT keyword. what causes this error? Both 'select' statements when run by themselves, complete successfully.
[code]WITH A1 AS WITH A1 AS ( SELECT MIN (VAPS_RPT_INTV_DMSN.INTV_DT), VAPS_RPT_INTV_DMSN.RPT_ID, VAPS_RPT_INTV_DMSN.RPT_INTV_ID FROM APS.VAPS_RPT_INTV_DMSN, APS.VAPS_RPT_CL_INTV_DMSN WHERE APS.VAPS_RPT_INTV_DMSN.RPT_INTV_ID =
I am using JDBC to run a few queries from my Java program (multi-threaded one).I am facing an issue where a select statement is blocking a delete statement. From the java code point of view, there are 2 different threads accessing the same tables (whith different DB connection objects).
When the block occurs (which i was able to find out from the java thread dump that there is a lock on oracle), the below is the output:
SQL> SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS') 2 || ' User '||s1.username || '@' || s1.machine 3 || ' ( SID= ' || s1.sid || ' ) with the statement: ' || sqlt2.sql_text ||' is blocking the SQL statement on '|| s2.username || '@' 4 5 || s2.machine || ' ( SID=' || s2.sid || ' ) blocked SQL -> ' 6 ||sqlt1.sql_text AS blocking_status FROM v$lock l1, v$session s1, v$lock l2 , 7 v$session s2,v$sql sqlt1, v$sql sqlt2 8 WHERE s1.sid =l1.sid 9 AND s2.sid =l2.sid AND sqlt1.sql_id= s2.sql_id AND sqlt2.sql_id= s1.prev_sql_id AND l1.BLOCK =1 10 AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2; [code]...
From the above it can be seen that a select statement is blocking a delete. Unless the select is select for Update, it should not block other statements is not it ?
I need to generate a select query in runtime and store the results of it into a file.Each time the column name and table name in the query will differ.Now im able to generate the select query through for cursor but problem is to store the results to the file.I tried using plsql table,im able to get the values to that table and store the results to a file,but the results of the query is more then 10000 lines (it might increase also)where only 4000 characters where able to store in the plsql table.so rest of them are not stored in the file.
Id Country city 1 US 2 US Boston 3 Boston 4 US Newyork 5 London 6 Japan Tokyo
Im looking for a query which returns results based on both city and country passed.
If i pass country US and city Boston it should return row2 with US and Boston row If i pass country null and city Boston it should return row3 If i pass country UK and city Boston it should return row3 If i pass country UK and city London it should return row5
i.e. If country/city combination exists in DB return that row Else city row should be returned.
I am having trouble figuring out why an update statment is ignoring the primary kiy index when performing an update through the application. The index IS used when the update is run from sql*plus or other sql tools.
The statement is very simple: update ITEM_MASTER set COST = :1 where SMARTPART_NUM = :2; ITEM_MASTER has unique, primary key index on SMARTPART_NUM
When I use OEM and other tools, I can see the index is not used in the exlain plan, and the query has a high CPU cost due to the full table scan. The table is analyzed, lately using 100%. Table rowcount is 229768
SELECT column_name, num_distinct, num_buckets, histogram, trunc(last_analyzed) ANALYZED FROM USER_TAB_COL_STATISTICS WHERE table_name = 'ITEM_MASTER' AND column_name = 'SMARTPART_NUM';
The database is Oracle Database 10g Release 10.2.0.4.0 - 64bit Production (on Windows, standard edition)
Here are parameters related to optimizer: optimizer_dynamic_sampling integer 2 optimizer_features_enable string 10.2.0.4 optimizer_index_caching integer 0 optimizer_index_cost_adj integer 100 optimizer_mode string ALL_ROWS optimizer_secure_view_merging boolean TRUE
We've tried rebuilding the indexes then re-analyzing, dropping stats and locking at zero, using various sample sizes; none have any impact.
We wondered if the bind variables are causing this, though in testing outside the application, we tried pl/sql and sql batches to mimic the passing of values into the binds, and all still used the index.
Other than putting a hint into SQL in the application (we'd have to ask for a customization), how can I "fix" the database to use the index? In other words, how can I make the database see that the cost of a FTS is much higher than using the index, whenever it sees these updates (either from sql or the application)? This is the simplest example, but we have 4-5 SQL updates on this and other tables that are ignoring the indexes and using full table scans, so we'd like to fix it for all.
I have the following problem. When I used in the IN-Statement fixed values e.q. 197321,197322,197323 ..., the index i_tab2_index works fine (index range scan).
But when I used in the IN-Statement an Sub-Select, the index i_tab2_index doesn't work (fast full scan)!My scale indices and used Selects:
CREATE INDEX i_tab1_index ON tab1 ( datum, flag_inst ); CREATE INDEX i_tab2_index ON tab2 ( tab2Idx, kontro ); SELECT count(epidx) as rowAnz FROM tab2 WHERE tab2Idx IN ( SELECT tab1IDX FROM tab1 WHERE datum BETWEEN '20120117' AND '20120117' AND flag_inst = '1' ) AND kontro = '9876521' [code]...
mbr has 60,000 rows and member has 60,000 rows approx. two tables have indexes on ssn, and citi_no on them.
PK of mbr : mbr_id PK of member : mbr_id
other columns are not PK, and have no index on it.
I'm wondering why the statment doesn't use index while ssn and citi_no have index.
MERGE INTO mbr t USING (SELECT mbr_id,citi_no FROM member) a ON (t.ssn = a.citi_no) WHEN MATCHED THEN UPDATE SET t.asis_mbr_id = a.mbr_id where t.ssn not in(select ssn from mbr group by ssn having count(*) > 1)
Is there a way I can find what the last date/time and index was used for a select...
I have a table with several indexes on them, which I beleive are not being accessed.
I use the following the query to find indexes that where not accessed in a while but this I believe is limited my my workload repository retention, which is set to 90 days.
select index_name from dba_indexes where table_name='<table name>' and index_name not in (select c1 from( select p.object_name c1, p.operation c2, p.options c3, count(1) c4 from dba_hist_sql_plan p, dba_hist_sqlstat s where p.object_owner = 'MTAS' and p.operation like '%INDEX%' and p.sql_id = s.sql_id group by p.object_name, p.operation, p.options order by 1,2,3))
Without increasing my repository retention is there a way I can get the last date/time, which an index was used instead of just saying it has not been used in 90 days (retention setting). Is this information kept in the SQL plan?
Is it a possible to create table using clause below together with index ?
create table the_table as select col1, col2 from table2
I got procedure which create a table in the schema B. The procedure is called from schema A. But when I write into procedure query for create index then I got a error:
ORA-01031: insufficient privileges when ...executing
Therefore I think about to create table together with index.
