CODEWITHSUNDEEP
sqloracle-databaseindexingbitmap
Eduardorf
Eduardorf

Reputation: 3

Creating bitmap index- ora-00921

I'm trying to create a bitmap index. My code is the following:

CREATE BITMAP INDEX citizen_idx
ON profiles (contracts.citizenID)
FROM profiles JOIN contracts
USING (citizenID) ;

And I get the following error:

ORA-00921: unexpected end of SQL command

Does anybody know what's wrong? Thanks!

Upvotes: 0

Views: 388

Answers (2)

psaraj12
psaraj12

Reputation: 5072

You cannot use ANSI JOIN syntax in BITMAP Join INDEXES. It is mentioned in this link

The same index you can create using old syntax like below

      CREATE BITMAP INDEX citizen_idx
      ON profiles (contracts.citizenID)
      FROM profiles,contracts
      where profiles.citizenID=contracts.citizenID;

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142705

I don't have your tables so I improvized. Basically, your syntax was wrong. Don't use explicit JOIN.

SQL> CREATE TABLE profiles (citizenid NUMBER);

Table created.

SQL> CREATE TABLE contracts
  2  (
  3     citizenid   NUMBER PRIMARY KEY,
  4     contractid  NUMBER
  5  );

Table created.

SQL> CREATE BITMAP INDEX idx
  2     ON profiles (p.citizenid)
  3     FROM profiles p, contracts c
  4     WHERE c.citizenid = p.citizenid;

Index created.

SQL>

Upvotes: 1

Related Questions