Oracle 12c New Feature – Multiple Indexes On The Same Column Or Set of Columns

By | Oracle Tips, Technology Tips & Tricks | No Comments
Starting with Oracle 12c, multiple indexes on same column or same set of columns can be created, as long as only one index is visible, and the indexes are physically different. It is not possible to create two bitmap indexes on the same column or set of columns. Example: Create a table ind_test1 SQL> create table ind_test1(id number, dept  varchar(20), start_dt date); Table created. Insert rows SQL>  insert into ind_test1 values(111, 'TEST', to_date('01/01/2000','mm/dd/yyyy')); 1 row created. SQL>  insert into ind_test1 values(222, 'DEVL',  to_date('10/25/2011','mm/dd/yyyy')); 1 row created. SQL>  insert into ind_test1 values(333, 'TEST',to_date('05/22/1999','mm/dd/yyyy')); 1 row created. Select * from ind_test1 table SQL> select * from ind_test1; ID DEPT                 START_DT ---------- -------------------- --------- 111 TEST                 01-JAN-00 222 DEVL                 25-OCT-11 333 TEST                 22-MAY-99     Create the first index on start_dt column. By default, Oracle creates B-tree index. SQL>  create index idx1_ind_test1 on ind_test1(start_dt); Index created.   Now, create a second invisible index on the same column SQL> create index idx2_ind_test1 on ind_test1(start_dt) invisible; create index idx2_ind_test1 on ind_test1(start_dt) invisible * ERROR at line 1: ORA-01408: such column list already indexed   Index failed because these 2 are the same type of indexes. You can create a bitmap index on the same column. SQL>  (more…)

Why You Should Consider Remote Database Support

By | Oracle, SAP HANA, SAP Sybase, SQL Server, Sybase, Technology Tips & Tricks | No Comments
According to Manpower’s 2015 Talent Shortage Survey, 38% of hiring managers worldwide are struggling to fill positions in their organizations. The talent shortage isn’t quite as bad in the U.S. as it is in many parts of the world, but even here, nearly one in three (32%) hiring managers report they are struggling. The most common reasons given for hiring difficulties are “Lack of Applicants” (35%) and “Lack of Technical Competencies” (34%). If you’ve tried to fill a vacancy in IT lately, particularly in a high-demand specialty like database management, I am probably not telling you anything new. Database management is a staffing problem because it contains so many specialties. Large organizations may be able to field teams of specialists, but most small to medium-sized businesses run very lean and can’t afford to hire a team member who only works on one aspect of database management. At this level, each team member is expected to master two or three aspects, and such skilled practitioners are even more difficult to find and hire than single-aspect specialists. When positions go unfilled, critical aspects of IT, from your production system to security, can be put at risk. If you try to cover the (more…)

Oracle to Sybase Database Migration Tips

By | Sybase Tips, Technology Tips & Tricks | No Comments

One of the most important parts of database migration is having a full understanding of the current systems and the business applications that they support. Without this fundamental knowledge, you will not only run the risk of missing key components in the system, but you will also not be able to strategically improve the performance of the database. Read More

SAP IQ 16.0 – How To Tutorials

By | Sybase Tips, Technology Tips & Tricks | No Comments

SAP IQ 16.0 is yet another milestone in the evolution of SAP’s analytical engine that originated from Sybase IQ. SAP created entire series of how to tutorials on SAP IQ. They reach from 15.4 to 16.0 and also include SCC 3.3.

Below are a list of YouTube videos, created by SAP engineers to bring you behind the scenes of SAP IQ.

There is a total of over 30 videos available.

Read More

Sybase IQ Versioning and Locks

By | Sybase Tips, Technology Tips & Tricks | No Comments

In Sybase IQ no two users can modify data in the same table at the same time. When you try to do this you will get an error in your application, as well as in the IQ message file. In IQ you can also run into problems when your transaction tries to modify something but hits an object or data that has been created after you started your transaction. Confusing? Read on and you will understand. Read More

Reverse Engineer Sybase IQ dbspaces Without an ER Tool!

By | Sybase Tips, Technology Tips & Tricks | No Comments

For Sybase IQ it is sometimes handy to reverse engineer the statements that were used to create the various IQ dbspaces. You can do this with Power Designer but not everybody has access to the tool.

Below you will find a script that does a basic attempt to reverse engineer the statements for the “create database” and “create dbspace” commands. The script is not complete with regard to all the possible options for these statements. Feel free to make adjustments or mail the author when you miss something.

NB: Written specifically for IQ 15 !

Read More

Sybase IQ Multiplex Nodes and Server Overview

By | Sybase Tips, Technology Tips & Tricks | No Comments

IQ_SYSTEM_MAIN, IQ_SHARED_TEMP, and IQ user main dbspaces are shared by all multiplex servers, and all servers need access to the same physical file. Data managed by SQL Anywhere is not shared. Each node requires a separate copy of such data.  Each server has its own set of files in IQ_SYSTEM_TEMP and IQ_SYSTEM_MSG. Read More

Sybase IQ Index Tips and Tricks

By | Sybase Tips, Technology Tips & Tricks | No Comments

Parallel = the holy grail of speed! Sometimes, but in this case it does.

Why on Earth does IQ only allow you to create indexes with parallel option? Ask Sybase!

CREATE  HG index idx_1_HG ON <table> (column_1);
CREATE LF index idx_2_LF ON <table> (column_2);
CREATE HG index idx_3_HG ON <table> (column_3);
...... on and on
Read More

%d bloggers like this: